Calculating Two Different Ranges of Working Hours Between Two Dates

[Major Aly]

Hello,

I've run into dilemma which I though I would overcome easily but ended in getting different results.

So I've been using the formula provided by Barry Houdini here: 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 work days 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, 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.

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

DC_CREATION_DATE​	ACTUAL_END_DATE​	EXPECTED_RESOLUTION_DATE​	1ST WORKING HOURS​	2ND WORKING HOURS​
27/04/2021 14:22:11​			6:00:00​	11:05:17​
01/04/2021 06:04:46​	01/04/2021 17:10:03​		2:28:13​	6:01:48​
01/04/2021 07:26:25​	01/04/2021 13:28:13​		2:56:19​	6:29:57​
01/04/2021 07:26:22​	01/04/2021 13:56:19​		0:00:00​	0:00:00​
01/04/2021 22:43:27​	01/04/2021 23:06:52​		9:36:02​	################​
01/04/2021 13:23:58​	02/04/2021 18:24:31​		5:41:12​	8:58:21​

Anyone who can help me in this regard?

 

[Major Aly]

Anyone?

 

[Excel938393]

Did you ever figure this out? I'm looking for the same thing...

 

[Major Aly]

Did you ever figure this out? I'm looking for the same thing...

Yes I did.

Since 2am in my scenario was of the next day, adding +1 to the 2am value (Upper 2/End Time 2) solved the issue.

I was still running 2 versions of formulas and then adding up the resulting values.

 

[Excel938393]

Yes I did.

Since 2am in my scenario was of the next day, adding +1 to the 2am value (Upper 2/End Time 2) solved the issue.

I was still running 2 versions of formulas and then adding up the resulting values.

I'll give this a try. Thanks!!!