# Calculate elapsed time between two dates/times, but exclude certain time periods

#### NewOrderFac33

##### Well-known Member
Good morning, I need to calculate the time between a task coming in (E8) and it being actioned (F8), but if something comes in late in the day and doesn't get picked up until the following morning, I need to exclude the time between 18:30 and 08:00 the following morning to reflect our operating hours. So if something came in at 18:15 on Monday and wasn't picked up until 08:15 on Tuesday, the formula would return 30 minutes (18:15-18:30)+ (08:00-08:15)

Ideally, I'd also like to exclude any time between 18:30 on Friday and 08:00 the following Monday, so if something came in on Friday at 18:20, but wasn't actioned until 08:10 the following Monday, the formula would return 20 minutes (18:20-18:30)+(08:00-08:10).

Is that even possible?
Pete

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

##### Well-known Member
if Your Time & Date for Coming is in E8 and Time & Date for Going is in F8. Then Use This:
Excel Formula:
``=IF(NETWORKDAYS(B1,C1)=0,0,IF(NETWORKDAYS(B1,B1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,30,0)-TIME(8,0,0))+MIN(18.5/24,MOD(C1,1))-TIME(8,0,0),IF(NETWORKDAYS(C1,C1)=0,(NETWORKDAYS(B1,C1)-1)*(TIME(18,30,0)-TIME(8,0,0))+TIME(18,30,0)-MAX(8/24,MOD(B1,1)),MIN(18.5/24,MOD(C1,1))-MAX(8/24,MOD(B1,1))+(NETWORKDAYS(B1,C1)-1)*(TIME(18,30,0)-TIME(8,0,0)))))``

##### Well-known Member
And if your coming and going Date is in C8 & D8 . Use This:
Excel Formula:
``=IF(NETWORKDAYS(C8,D8)=0,0,IF(NETWORKDAYS(C8,C8)=0,(NETWORKDAYS(D8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0))+MIN(18.5/24,MOD(F8,1))-TIME(8,0,0),IF(NETWORKDAYS(D8,D8)=0,(NETWORKDAYS(C8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0))+TIME(18,30,0)-MAX(8/24,MOD(E8,1)),MIN(18.5/24,MOD(F8,1))-MAX(8/24,MOD(E8,1))+(NETWORKDAYS(C8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0)))))``

And Don't Forgot Change Number Format to [h]:mm (From custom at Type Section)

#### NewOrderFac33

##### Well-known Member
And if your coming and going Date is in C8 & D8 . Use This:
Excel Formula:
``=IF(NETWORKDAYS(C8,D8)=0,0,IF([B]NETWORKDAYS(C8,C8)[/B]=0,([B]NETWORKDAYS(D8,D8[/B])-1)*(TIME(18,30,0)-TIME(8,0,0))+MIN(18.5/24,MOD([B]F8[/B],1))-TIME(8,0,0),IF([B]NETWORKDAYS(D8,D8[/B])=0,(NETWORKDAYS(C8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0))+TIME(18,30,0)-MAX(8/24,MOD([B]E8[/B],1)),MIN(18.5/24,MOD([B]F8[/B],1))-MAX(8/24,MOD([B]E8[/B],1))+(NETWORKDAYS(C8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0)))))``

And Don't Forgot Change Number Format to [h]:mm (From custom at Type Section)
With 01/12/2020 18:15:00 in C8 and 02/12/2020 08:15:00 in D8, your formula currently returns 2:30, when by my reckoning it should be 30 minutes (18:15-18:30 + 08:00-08:15)
I also noticed in your formula a few references to E8 and F8 and also a couple of NETWORKDAYS functions referencing the same cell. Are these correct?
Thank you!
Pete

##### Well-known Member

you told your times is in E8 & F8 and i write first formula for it. For the Last Situation, you should use first formula but change all E8 to C8 , and Then Change all F8 to D8 . Then formula is this:
Excel Formula:
``=IF(NETWORKDAYS(C8,D8)=0,0,IF(NETWORKDAYS(C8,C8)=0,(NETWORKDAYS(D8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0))+MIN(18.5/24,MOD(D8,1))-TIME(8,0,0),IF(NETWORKDAYS(D8,D8)=0,(NETWORKDAYS(C8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0))+TIME(18,30,0)-MAX(8/24,MOD(C8,1)),MIN(18.5/24,MOD(D8,1))-MAX(8/24,MOD(C8,1))+(NETWORKDAYS(C8,D8)-1)*(TIME(18,30,0)-TIME(8,0,0)))))``

And Result Should be 15 min not 30 min.

Last edited:

#### NewOrderFac33

##### Well-known Member
01/12/2020 18:15 to 01/12/2020 18:30 is 15 minutes
02/12/2020 08:00 to 02/12/2020 08:15 is also 15 minutes
the two add up to 30 minutes.
Forgive me, but I also don't understand why you have a number of NETWORKDAYs instances that reference the same cell twice e.g. IF(NETWORKDAYS(C8,C8)=0 and IF(NETWORKDAYS(C8,C8)=0 ?
Regards
Pete

##### Well-known Member
Yes. But The Day of 01/12/2020 is Sunday and don't Count.
IF(NETWORKDAYS(C8,C8)=0 this is for times the Coming Date is Holiday ( Saturday & Sunday)
IF(NETWORKDAYS(D8,D8)=0 this is for times the Going Date is Holiday ( Saturday & Sunday)

#### jasonb75

##### Well-known Member
On the assumption that the dates and times entered in E8 and F8 will always be in the working day, this appears to give the correct results, any times before 08:00, after 18:30 or weekend dates will not work correctly.
Book1 (version 1).xlsb
EFG
7InActionedDifference
801/12/2020 18:1502/12/2020 08:1500:30:00
927/11/2020 18:2030/11/2020 08:1000:20:00
1002/12/2020 12:1502/12/2020 15:3503:20:00
Sheet3
Cell Formulas
RangeFormula
G8:G10G8=MOD(NETWORKDAYS(E8,F8,E8)*("18:30"-"08:00")-(1-MOD(F8-E8,1)),1)

It will show some strange results if one of the cells is empty, but that can easily be fixed with an IF should it be needed.

Replies
3
Views
205
Replies
2
Views
192
Replies
4
Views
385
Replies
4
Views
644
Replies
5
Views
153

1,127,802
Messages
5,626,984
Members
416,213
Latest member
neflerine

### 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.

### Which adblocker are you using?

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