Calculating Two Different Ranges of Working Hours Between Two Dates

Major Aly

New Member
Joined
Mar 3, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Excel938393

New Member
Joined
Aug 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Did you ever figure this out? I'm looking for the same thing...
 

Major Aly

New Member
Joined
Mar 3, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Aug 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!!!
 

Forum statistics

Threads
1,141,068
Messages
5,704,094
Members
421,327
Latest member
Msh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top