Calculate total time taken to complete task in working days (6am to 6pm)

[Wibab]

Hello,





If I have a start date/time, and an end date/time, how would I calculate how long a task is taken to complete (in days), if I only account for workdays and working shift between 6am - 6pm?





Examples below:





Data obtained 15/7/2023 20:59


Work completed 17/7/2023 15:50


So work in undertaken on 16/7 6am - 6pm (12 hours) + 17/7 6am - 3.50pm (9.83 hours).


Result should = 21 hours 50 minutes = 21.83 hours = 1.82 days





Data obtained 15/6/2024 18:13


Work completed 17/6/2024 12:16


So work is undertaken on 17/6 6am - 12.16pm (15/6 and 16/6 are on weekend).


Result should = 6 hours 16 minutes = 6.27 hours = 0.52 days.





Thank you

 

[Wibab]

*correction: first example should return a result = 9.83 hours = 0.82 days, because 16/7/2023 is a Sunday.

 

[Wibab]

Another example:

Data obtained 15/5/2024 15:42

Work completed 17/5/2024 15:13

So work in undertaken on (15/5/24 3.42pm - 6pm) + (16/5/24 6am - 6pm) + (17/5/24 6am - 3.13pm)

Result should = 23 hours 31 minutes = 23.52 hours = 1.96 days

 

[Cubist]

I've found some ideas from an older thread and modified them to your criteria. Try:

Book1
	A	B	C
1	Start	End	Worked days
2	7/15/23 20:59	7/17/23 15:50	0.82
3	6/15/24 18:13	6/17/24 12:16	0.52
4	5/15/24 15:42	5/17/24 15:13	1.96
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=2*LET( start,A2,end,B2,upTime,18/24,lowTime,6/24, (NETWORKDAYS(start,end)-1)*(upTime-lowTime) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upTime,lowTime),upTime) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upTime,lowTime) )

 

[Wibab]

Perfect, that's exactly what I'm after.
Thank you very much for your help!