Calculating Two Different Ranges of Working Hours Between Two Dates

Major Aly

New Member
Joined
Mar 3, 2021
Messages
7
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,136,765
Messages
5,677,618
Members
419,707
Latest member
Anna vib

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