Calculate worked hours based upon criteria

[Skybluekid]

Hi All,


I have a sheet where it records a time when one of our trucks goes into a dealer. It also records the time the trucks leaves the dealer. I would like to workout how long the vehicle has been stood in the dealer. That is easy to do, but in order to be accurate, I would like the dealers opening hours to be considered.

For example, The truck enters a dealer, and we have a time stamp of 5/8/2019 08:00. The truck leaves on the 12/8/19 12:00. The Dealer opening hours are 08:00 - 23:00 Mon-Fri 08:00 - 12:00 Saturday Closed on Sunday.

So in the above scenario, the answer would be 75hrs


Thanks in Advance

 

[jorismoerings]

Hi,

I can't match your answer but i assume you've made a calculation error.
Check my table and formula and try if it suits your need:

Book1
	A	B	C	D	E
1	In	Out	Total
2	5-8-2019 08:00	12-8-2019 12:00	83:00:00
3
4	05-08-2019	Monday	08:00	23:00	15:00
5	06-08-2019	Tuesday	08:00	23:00	15:00
6	07-08-2019	Wednesday	08:00	23:00	15:00
7	08-08-2019	Thursday	08:00	23:00	15:00
8	09-08-2019	Friday	08:00	23:00	15:00
9	10-08-2019	Saturday	08:00	12:00	04:00
10	11-08-2019	Sunday
11	12-08-2019	Monday	08:00	12:00	04:00
12					83:00:00
Sheet3

Cell Formulas
Range	Formula
C2	=(NETWORKDAYS.INTL(A2,B2,1)-1)*("23:00"-"08:00")+IF(NETWORKDAYS.INTL(B2,B2,1),MEDIAN(MOD(B2,1),"23:00","08:00"),"23:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1)*MOD(A2,1),"23:00","08:00")+(NETWORKDAYS.INTL(A2,B2,"1111101")-1)*("12:00"-"08:00")+IF(NETWORKDAYS.INTL(B2,B2,"1111101"),MEDIAN(MOD(B2,1),"12:00","08:00"),"12:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,"1111101")*MOD(A2,1),"12:00","08:00")

 

[Skybluekid]

That is spot on thank you

 

[Sam_D_Ben]

Hello Jorismoerings, I was wondering why i am getting a different figure with your formula.

Excel 2013/2016
	A	B	C	D	E
1	In	Out	Total
2	5/8/2019 8:00	12/8/2019 12:00	100.79
3
4	8-May-19	Monday	8:00	23:00	15:00
5	8-Jun-19	Tuesday	8:00	23:00	15:00
6	8-Jul-19	Wednesday	8:00	23:00	15:00
7	8-Aug-19	Thursday	8:00	23:00	15:00
8	8-Sep-19	Friday	8:00	23:00	15:00
9	8-Oct-19	Saturday	8:00	12:00	4:00
10	8-Nov-19	Sunday
11	8-Dec-19	Monday	8:00	12:00	4:00
12					83:00:00
Sheet1

Cell Formulas
Range	Formula
C2	=(NETWORKDAYS.INTL(A2,B2,1)-1)*("23:00"-"08:00")+IF(NETWORKDAYS.INTL(B2,B2,1),MEDIAN(MOD(B2,1),"23:00","08:00"),"23:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1)*MOD(A2,1),"23:00","08:00")+(NETWORKDAYS.INTL(A2,B2,"1111101")-1)*("12:00"-"08:00")+IF(NETWORKDAYS.INTL(B2,B2,"1111101"),MEDIAN(MOD(B2,1),"12:00","08:00"),"12:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,"1111101")*MOD(A2,1),"12:00","08:00")

 

[jorismoerings]

@Sam_D_Ben,

Hi, i'm getting the same result as you howeverthe devil is in the details.
The OP ask a question regarding a timeframe and from his/her originating country i deducted the date/time notation was UK.

5/8/2019 08:00. The truck leaves on the 12/8/19 12:00

doesn't mean may 8th tll december 8th but August 5th till August 12th.

When entered, these are the results:

Book1
	A	B	C
1	In	Out	Total
2	8 May 2019 08:00	8 December 2019 12:00	100,7916667
3	5 August 2019 08:00	12 August 2019 12:00	3,458333333
Sheet1

Cell Formulas
Range	Formula
C2	=(NETWORKDAYS.INTL(A2,B2,1)-1)*("23:00"-"08:00")+IF(NETWORKDAYS.INTL(B2,B2,1),MEDIAN(MOD(B2,1),"23:00","08:00"),"23:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1)*MOD(A2,1),"23:00","08:00")+(NETWORKDAYS.INTL(A2,B2,"1111101")-1)*("12:00"-"08:00")+IF(NETWORKDAYS.INTL(B2,B2,"1111101"),MEDIAN(MOD(B2,1),"12:00","08:00"),"12:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,"1111101")*MOD(A2,1),"12:00","08:00")
C3	=(NETWORKDAYS.INTL(A3,B3,1)-1)*("23:00"-"08:00")+IF(NETWORKDAYS.INTL(B3,B3,1),MEDIAN(MOD(B3,1),"23:00","08:00"),"23:00")-MEDIAN(NETWORKDAYS.INTL(A3,A3,1)*MOD(A3,1),"23:00","08:00")+(NETWORKDAYS.INTL(A3,B3,"1111101")-1)*("12:00"-"08:00")+IF(NETWORKDAYS.INTL(B3,B3,"1111101"),MEDIAN(MOD(B3,1),"12:00","08:00"),"12:00")-MEDIAN(NETWORKDAYS.INTL(A3,A3,"1111101")*MOD(A3,1),"12:00","08:00")