Calculating Elapsed Time between two dates and ignoring out of hours

New Member
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?

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
Hi,

=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``

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

GraH

Well-known Member
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
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
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)))

Replies
4
Views
394
Replies
1
Views
103
Replies
4
Views
362
Replies
0
Views
77
Replies
7
Views
448

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.

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

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