Calculating Elapsed Time between two dates and ignoring out of hours

Adrianc65

New Member
Joined
Apr 26, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi

Trying to calculate elapsed time between two dates and time but also wanting to ignore time beyond 17:00 and 07:00 each work day and the next and all weekends. So the clock is only on Monday to Friday 07:00 to 17:00.

In that scenario what would the excel formula be for example below

Start Time: 21/04/2020 16:35 [cell a2]
Stop Time: 28/04/2020 16:30 [cell a3]

I can calculate elapsed time =A3-A2 and then format cells [hh:mm] to give me total elapsed time but how do i exclude the out of hours time from that?

Thanks in advance
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

What about this formula I once found on the Excelforum.
=TIME(17,0,0)-MAX(MOD(A2,1),TIME(7,0,0))+MAX(0,(NETWORKDAYS.INTL(A2,A3,1)-2)*(TIME(10,0,0)))+MIN(TIME(17,0,0),MOD(A3,1))-TIME(7,0,0)
Formatted as
Rich (BB code):
[H]:mm
returns for your example 49:55

25 minutes for Tue
Wed -> Fr: 10hrs *3 = 30 hrs
Mo: 10 Hrs
Tue: 9 Hrs 30
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
To be fair, I've been playing around with other dates, that's not always guaranteeing a correct result. What if start and/or end date fall in the weekend?
So it might be better to include some helper columns.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Or extend the formula a bit to test if start and end data fall in the weekend or not.
= MAX(0,WEEKDAY(A2,2)<6)*(TIME(17,0,0)-MAX(MOD(A2,1),TIME(7,0,0))) +MAX(0,(NETWORKDAYS.INTL(A2,A3,1)-2)*(TIME(10,0,0)))+MAX(0,WEEKDAY(A3,2)<6)*(MIN(TIME(17,0,0),MOD(A3,1))-TIME(7,0,0))
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
712
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Revisited this as I was still not sure this is watertight, and found some redundancy in my previous formula.
What if your start and end hour are outside shift? Like start after 17:00 or end before 7:00? Are those even possible scenarios?

=(WEEKDAY(A2,2)<6)*(TIME(17,0,0)-MAX(MIN(MOD(A2,1),TIME(17,0,0)),TIME(7,0,0)))+MAX(0,(NETWORKDAYS.INTL(A2,A3,1)-2)*(TIME(10,0,0)))+(WEEKDAY(A3,2)<6)*(MAX(0,MIN(TIME(17,0,0),MOD(A3,1))-TIME(7,0,0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,211
Messages
5,640,887
Members
417,177
Latest member
njosh

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