Calculating Elapsed Time between two dates and ignoring out of hours

[Adrianc65]

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

 

[GraH]

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

[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]

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]

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]

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