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

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
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?
Thanks in advance
Pete
 

Some videos you may like

Excel Facts

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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)))))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Sep 26, 2011
Messages
1,255
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 26, 2011
Messages
1,255
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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