# Calculating Two Different Ranges of Working Hours Between Two Dates

#### Major Aly

##### New Member
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

Excel 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:
Excel Formula:
``````=(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?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Anyone?

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

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.

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!!!

Replies
1
Views
243
Replies
24
Views
1K
Replies
3
Views
642
Replies
16
Views
1K
Replies
5
Views
173

1,218,899
Messages
6,145,098
Members
450,590
Latest member
Naneng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back