Two Separate Ranges of Business Hours (Like Shifts) Between Date Ranges

2
votes

I've run into a dilemma that I thought I would overcome easily but ended in getting different results.

So I've been using the following formula:

=(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+IF(NETWORKDAYS(N6,N6),MEDIAN(MOD(N6,1),$F$2,$E$2),$F$2)-MEDIAN(NETWORKDAYS(H6,H6)*MOD(H6,1),$F$2,$E$2)

This formula works perfectly, as I've modified it to use NETWORKDAYS.INTL because all workdays are working days, but the working hours are from 8:00 AM to 11:00 PM.

My modified code is:

=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],"0000000")-1)*(upper-lower)
+IF(NETWORKDAYS.INTL([@[ACTUAL_END_DATE]],[@[ACTUAL_END_DATE]],"0000000"),MEDIAN(MOD([@[ACTUAL_END_DATE]],1),upper,lower),upper)
-MEDIAN(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[DC_CREATION_DATE]],"0000000")*MOD([@[DC_CREATION_DATE]],1),upper,lower)

Where:

  • DC_CREATION_DATE = Start Date
  • ACTUAL_END_DATE = End Date
  • upper = Named Range containing the End Time i.e. 11:00 PM
  • lower = Named Range containing the Start Time i.e. 8:00 AM

Now the problem is that for a particular month, the working hours in a day change to shifts:

  • Start Time 1 = 11:00 AM
  • End Time 1 = 5:00 PM
  • Start Time 2 = 9:00 PM
  • End Time 2 = 2:00 AM

Any time outside the above-mentioned times are non-working hours. The TTs we receive CAN be outside working hours, but I will only calculate time duration inside working hours.

What I tried doing was to use the same formula twice on the same date range (first time, upper becomes 5:00 PM and lower becomes 11:00 AM and the result is stored in the cell, the second time, upper becomes 2:00 AM and lower becomes 9:00 PM and the result is stored in a different cell), and then add the two results in separate cells to get the overall working hours between the working hours range.

Alas, I thought it was that simple but what I'm seeing are different results, for both formulas when they're run. Because if the time is outside of the working range, even for start and end, it should return 0. (I believe I can overcome this by using IF)

The ######## is the field showing the value in negative. Since I'm formatting all cells to [h]:mm

OUTPUT

Anyone who can help me in this regard?

1
excelexcel-formulacalculation
Are you sure you're not getting ########## because you're calculating upper-lower based upon times as opposed to date/time format (which would give a negative and I would expect ######### for negative durations in excel(!) )? i.e. 2nd shift has upper of 2AM and lower of 9PM, so if you're refined to time format alone then you'd need to have some kind of an if(date_end > date_start, 2nd_working_hrs = time_end + 1 - time_start, time_end-time_start) (exchange end, start with upper, lower resp. as required - but you get my gist, right? See this soln. which is similar (and upvote mine! :) Lol – JB-007
...(continued): stackoverflow.com/questions/67351953/… PS: if this works give me a chance to propose for bounty - it's the only reason I found / looked at this Q sadly :( :) – JB-007

1 Answers

2
votes

Since your Upper 2 time is next day, you need to enter it as such.

The underlying value of 2:00 AM is 0.083333333. To make it next day, enter it as 1.083333333. You can stil format it as 2:00 AM if you wish, it's the underlying value your formula is using.

Some examples, with your formula unchanged (other than including Upper/Lower 1 and 2)

enter image description here