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

[NewOrderFac33]

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

 

[maabadi]

if Your Time & Date for Coming is in E8 and Time & Date for Going is in F8. Then Use This:

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

And if your coming and going Date is in C8 & D8 . Use This:

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

And if your coming and going Date is in C8 & D8 . Use This:

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

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:

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

 

[NewOrderFac33]

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]

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]

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
	E	F	G
7	In	Actioned	Difference
8	01/12/2020 18:15	02/12/2020 08:15	00:30:00
9	27/11/2020 18:20	30/11/2020 08:10	00:20:00
10	02/12/2020 12:15	02/12/2020 15:35	03:20:00
Sheet3

Cell Formulas
Range	Formula
G8:G10	G8	=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.