Calculate time with consideration to cut-off and working hours

[ExcelNewbie2020]

I would like to calculate the time difference between completed and start date but with condition.. Please see below.. thank you..

Start	Completed	Expected Result (hh:mm)
01-08-22 10:00​	01-08-22 16:50​	3:50
03-08-22 10:00​	04-08-22 10:20​	7:20
06-08-22 11:00​	09-08-22 11:40​	25:40
09-08-22 13:00​	10-08-22 11:15​	2:15
10-08-22 15:00​	12-08-22 15:00​	15:00
10-08-22 15:00​	13-08-22 16:00​	25:00
22-08-22 16:00​	23-08-22 16:00​	7:00
25-08-22 16:00​	27-08-22 10:50​	10:50
Office Hours 9am-6pm
Condition
if the start date is <1PM Counting start from 1PM to 6PM (5hrs) of the same date
if the start date is >1PM Counting start the next day from 9AM to 6PM (9hrs)
Assuming it is not completed on the succeeding days 1 day = to 9hrs (within working hours)

 

[ExcelNewbie2020]

I would like to add.. holidays and weekends are included.. i mean the working hours and days are from 9am-6pm and 7 days a week.. thanks

 

[Legacy 143009]

It is a bit clumsy but works:

=INT((((9*120)-(HOUR(A2)*60)-MINUTE(A2))+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(9*60)))/60) & ":" & MOD(((((9*120)-(HOUR(A2)*60)-MINUTE(A2))+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(9*60)))),60)

 

[ExcelNewbie2020]

It is a bit clumsy but works:

=INT((((9*120)-(HOUR(A2)*60)-MINUTE(A2))+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(9*60)))/60) & ":" & MOD(((((9*120)-(HOUR(A2)*60)-MINUTE(A2))+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(9*60)))),60)

thank you very much sir.. there is a difference of 2 or 3 hours..

 

[Legacy 143009]

Yee, he worked 6.50 hours from 10:00 to 16:50.
Howcome you are expecting 3.50?

Edit: Ok now I read it. It will start from 13:00

 

[ExcelNewbie2020]

Yee, he worked 6.50 hours from 10:00 to 16:50.
Howcome you are expecting 3.50?

Edit: Ok now I read it. It will start from 13:00

yes, the idea is, if he clocked in before 1PM his work will start at 1PM onwards.. but if after or equal to 1PM, the work will start the next day at 9AM onwards..

 

[Legacy 143009]

=INT((IF(HOUR(A2)<13,300,0)+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(540)))/60) & ":" & INT(MOD((IF(HOUR(A2)<13,300,0)+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(540)))/60,1)*60)

 

[ExcelNewbie2020]

=INT((IF(HOUR(A2)<13,300,0)+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(540)))/60) & ":" & INT(MOD((IF(HOUR(A2)<13,300,0)+((DAY(B2)-DAY(A2)-1)*540)+((HOUR(B2)*60)+MINUTE(B2)-(540)))/60,1)*60)

thank you sir.. it works smoothly..

 

[Legacy 143009]

Glad İt did help.