Calculating Minutes Between Date/Time with Operating Hours

J

joelmb

New Member
Joined
Aug 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I'm currently on a placement where at the moment I'm trying to figure out a calculation too complex for my understanding.

We need to find a way to calculate the duration in minutes between two dates while considering different operating hours (Standard, 24/7, and 3rd party). Standard hours are 7am - 9pm M-F, 8am - 5pm Sat, and closed on Sunday.
We currently use Excel 2016.

Appreciate any help.

Thanks.

Operating Hours Formula.xlsx
ABCDEFGHIJKL
1CategoryStartEndOperating HoursDuration
2AAA07/07/2021 05:5509/07/2021 16:40Standard Operating HoursStandard Operating Hours
3BBB06/07/2021 04:3206/07/2021 15:0924*7 Operating HoursOpenClose
4CCC09/07/2021 14:3712/07/2021 09:55Standard Operating HoursMon - Fri07:0021:00
5DDD06/07/2021 11:4707/07/2021 12:513rd Party Defined Operating HoursSat08:0017:00
6EEE08/07/2021 15:3112/07/2021 06:4224*7 Operating HoursSunClosed
7FFF11/07/2021 09:1613/07/2021 15:23Standard Operating Hours
8
9
10
11
12
13Needed in formula:
14Calculate Duration between Start and End times
15Hours within Standard Operating Hours is needed, anything out of these hours shall be deducted from overall duration.
16
17However, for 24/7 operating hours, duration is simply total difference between start and end.
183rd Party Defined operating hours will need to remain as a manual input, so cell should be left blank.
19
Sheet1
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi,

Take a look at this and see if this helps

Book2
ABCDEFGHIJK
1CategoryStartEndOperating HoursDuration
2AAA7-7-2021 05:55:009-7-2021 16:40:00Standard Operating Hours37:40:00Standard Operating Hours
3BBB6-7-2021 04:32:006-7-2021 15:09:0024*7 Operating Hours10:37:00OpenClose
4CCC9-7-2021 14:37:0012-7-2021 09:55:00Standard Operating Hours18:18:00Mon - Fri07:00:0021:00:00
5DDD6-7-2021 11:47:007-7-2021 12:51:003rd Party Defined Operating Hours25:04:00Sat08:00:0017:00:00
6EEE8-7-2021 15:31:0012-7-2021 06:42:0024*7 Operating Hours87:11:00SunClosed
7FFF11-7-2021 09:16:0013-7-2021 15:23:00Standard Operating Hours22:23:00
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=IF(LEFT(D2,8)= "Standard",((NETWORKDAYS.INTL(B2,C2,"0000011",)-1)*($K$4-$J$4)+IF(NETWORKDAYS.INTL(C2,C2,"0000011",),MEDIAN(MOD(C2,1),$K$4,$J$4),$K$4)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"0000011",)*MOD(B2,1),$K$4,$J$4))+((NETWORKDAYS.INTL(B2,C2,"1111101",)-1)*($K$5-$J$5)+IF(NETWORKDAYS.INTL(C2,C2,"1111101",),MEDIAN(MOD(C2,1),$K$5,$J$5),$K$5)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111101",)*MOD(B2,1),$K$5,$J$5)),C2-B2)
 
Upvote 0
Solution
As addition to previous post:

if duration needs to be in minutes just format cell E2 into a Number format and the formula becomes
Excel Formula: 
=(IF(LEFT(D2,8)= "Standard",((NETWORKDAYS.INTL(B2,C2,"0000011",)-1)*($K$4-$J$4)+IF(NETWORKDAYS.INTL(C2,C2,"0000011",),MEDIAN(MOD(C2,1),$K$4,$J$4),$K$4)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"0000011",)*MOD(B2,1),$K$4,$J$4))+((NETWORKDAYS.INTL(B2,C2,"1111101",)-1)*($K$5-$J$5)+IF(NETWORKDAYS.INTL(C2,C2,"1111101",),MEDIAN(MOD(C2,1),$K$5,$J$5),$K$5)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111101",)*MOD(B2,1),$K$5,$J$5)),C2-B2))*1440
 
Upvote 0
jorismoerings said:
Hi,

Take a look at this and see if this helps

Book2
ABCDEFGHIJK
1CategoryStartEndOperating HoursDuration
2AAA7-7-2021 05:55:009-7-2021 16:40:00Standard Operating Hours37:40:00Standard Operating Hours
3BBB6-7-2021 04:32:006-7-2021 15:09:0024*7 Operating Hours10:37:00OpenClose
4CCC9-7-2021 14:37:0012-7-2021 09:55:00Standard Operating Hours18:18:00Mon - Fri07:00:0021:00:00
5DDD6-7-2021 11:47:007-7-2021 12:51:003rd Party Defined Operating Hours25:04:00Sat08:00:0017:00:00
6EEE8-7-2021 15:31:0012-7-2021 06:42:0024*7 Operating Hours87:11:00SunClosed
7FFF11-7-2021 09:16:0013-7-2021 15:23:00Standard Operating Hours22:23:00
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=IF(LEFT(D2,8)= "Standard",((NETWORKDAYS.INTL(B2,C2,"0000011",)-1)*($K$4-$J$4)+IF(NETWORKDAYS.INTL(C2,C2,"0000011",),MEDIAN(MOD(C2,1),$K$4,$J$4),$K$4)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"0000011",)*MOD(B2,1),$K$4,$J$4))+((NETWORKDAYS.INTL(B2,C2,"1111101",)-1)*($K$5-$J$5)+IF(NETWORKDAYS.INTL(C2,C2,"1111101",),MEDIAN(MOD(C2,1),$K$5,$J$5),$K$5)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111101",)*MOD(B2,1),$K$5,$J$5)),C2-B2)
Click to expand...

Hi,

That worked exactly as needed. Thank you for you help, I appreciate it!

As a secondary, would there be any way of adding bank holidays in to the mix so that the hours in those days are not calculated either?
 
Upvote 0
joelmb said:
As a secondary, would there be any way of adding bank holidays in to the mix so that the hours in those days are not calculated either?
Click to expand...
Yes, create a range somewhere on your sheet with dates and insert them into the formula. Never mind the way they're sorted, that has no bearing for the working of the formula.
I assume you want to bank holidays deducted from 24/7 as well. If Yes, change to this:
Book2.xlsx
ABCDEFGHIJKLM
1CategoryStartEndOperating HoursDuration in timeDuration in minutesBank Holidays
2AAA7-7-2021 05:55:009-7-2021 16:40:00Standard Operating Hours37:40:002260Standard Operating Hours28-12-2021
3BBB6-7-2021 04:32:006-7-2021 15:09:0024*7 Operating Hours10:37:00637OpenClose27-12-2021
4CCC9-7-2021 14:37:0012-7-2021 09:55:00Standard Operating Hours18:18:001098Mon - Fri07:00:0021:00:0030-8-2021
5DDD6-7-2021 11:47:007-7-2021 12:51:003rd Party Defined Operating Hours25:04:001504Sat08:00:0017:00:0031-5-2021
6EEE8-7-2021 15:31:0012-7-2021 06:42:0024*7 Operating Hours87:11:005231SunClosed3-5-2021
7FFF11-7-2021 09:16:0013-7-2021 15:23:00Standard Operating Hours22:23:0013435-4-2021
8FFF28-12-2021 09:16:0028-12-2021 15:23:00Standard Operating Hours00:00:0002-4-2021
91-1-2021
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IF(LEFT(D2,8)= "Standard",((NETWORKDAYS.INTL(B2,C2,"0000011",Bank_holidays)-1)*($J$4-$I$4)+IF(NETWORKDAYS.INTL(C2,C2,"0000011",Bank_holidays),MEDIAN(MOD(C2,1),$J$4,$I$4),$J$4)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"0000011",Bank_holidays)*MOD(B2,1),$J$4,$I$4))+((NETWORKDAYS.INTL(B2,C2,"1111101",Bank_holidays)-1)*($J$5-$I$5)+IF(NETWORKDAYS.INTL(C2,C2,"1111101",Bank_holidays),MEDIAN(MOD(C2,1),$J$5,$I$5),$J$5)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111101",Bank_holidays)*MOD(B2,1),$J$5,$I$5)),((NETWORKDAYS.INTL(B2,C2,"0000000",Bank_holidays)-1)*("24:00:00"-"00:00:00")+IF(NETWORKDAYS.INTL(C2,C2,"0000000",Bank_holidays),MEDIAN(MOD(C2,1),"24:00:00","00:00:00"),"24:00:00")-MEDIAN(NETWORKDAYS.INTL(B2,B2,"0000000",Bank_holidays)*MOD(B2,1),"24:00:00","00:00:00")))
F2:F8F2=E2*1440
Named Ranges
NameRefers ToCells
Bank_holidays=Sheet1!$L$2:$L$9E2:E8
 
Upvote 0
jorismoerings said:
Yes, create a range somewhere on your sheet with dates and insert them into the formula. Never mind the way they're sorted, that has no bearing for the working of the formula.
I assume you want to bank holidays deducted from 24/7 as well.
Click to expand...

Apologies, should've been more specific. Bank holidays only need to be deducted from the standard and 3rd Party Operating hours, the 24/7 OH's are for internet facing services etc. so will be calculated 24/7 no matter what.
 
Upvote 0
joelmb said:
Apologies, should've been more specific. Bank holidays only need to be deducted from the standard and 3rd Party Operating hours, the 24/7 OH's are for internet facing services etc. so will be calculated 24/7 no matter what.
Click to expand...
No problem, changed it towards your request.
Book2.xlsx
ABCDEFGHIJKL
1CategoryStartEndOperating HoursDuration in timeDuration in minutesBank Holidays
2AAA7-7-2021 05:55:009-7-2021 16:40:00Standard Operating Hours37:40:002260Standard Operating Hours28-12-2021
3BBB6-7-2021 04:32:006-7-2021 15:09:0024*7 Operating Hours10:37:00637OpenClose27-12-2021
4CCC9-7-2021 14:37:0012-7-2021 09:55:00Standard Operating Hours18:18:001098Mon - Fri07:00:0021:00:0030-8-2021
5DDD6-7-2021 11:47:007-7-2021 12:51:003rd Party Defined Operating Hours25:04:001504Sat08:00:0017:00:0031-5-2021
6EEE8-7-2021 15:31:0012-7-2021 06:42:0024*7 Operating Hours87:11:005231SunClosed3-5-2021
7FFF11-7-2021 09:16:0013-7-2021 15:23:00Standard Operating Hours22:23:0013435-4-2021
8FFF28-12-2021 09:16:0028-12-2021 15:23:00Standard Operating Hours00:00:0002-4-2021
91-1-2021
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=IF(LEFT(D2,8)= "Standard",((NETWORKDAYS.INTL(B2,C2,"0000011",Bank_holidays)-1)*($J$4-$I$4)+IF(NETWORKDAYS.INTL(C2,C2,"0000011",Bank_holidays),MEDIAN(MOD(C2,1),$J$4,$I$4),$J$4)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"0000011",Bank_holidays)*MOD(B2,1),$J$4,$I$4))+((NETWORKDAYS.INTL(B2,C2,"1111101",Bank_holidays)-1)*($J$5-$I$5)+IF(NETWORKDAYS.INTL(C2,C2,"1111101",Bank_holidays),MEDIAN(MOD(C2,1),$J$5,$I$5),$J$5)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111101",Bank_holidays)*MOD(B2,1),$J$5,$I$5)),C2-B2)
F2:F8F2=E2*1440
Named Ranges
NameRefers ToCells
Bank_holidays=Sheet1!$L$2:$L$9E2:E8
 
Upvote 0
You must log in or register to reply here.

Similar threads

earthworm
  • Question Question
Calculate Time and Date between 2 Date
Replies
8
Views
447
Dave Patton
D
D
  • Solved
Highlighting cells with date/time greater than 29 minutes past the hour
Replies
6
Views
608
Peter_SSs
Peter_SSs
S
  • Question Question
Calculate Turn Around Time 7 days working excluding Holiday and in Working Hours
Replies
3
Views
761
surevyas1984
S
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
J
  • Question Question
Calculate Hours Across Different Dates & Times
Replies
6
Views
1K
Proulxs
P

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top