Calculate TAT based on working hours(8am - 5pm), Microsoft Excel

I needed to calculate Turn Around Time from time stamps recorded at a site,

I have entry date and time, and exit date and time.

I adjust entry time so that all adjusted entry times are within 8am and 5pm: anytime less than 8am but greater than 12am would be adjusted to 8am whiles any time greater than 5pm but less than 12am would be adjusted to next day 8am

here is the formula =TRUNC(G43)+(IF((G43-TRUNC(G43))>=0.708332696762227,1.33333321758982,IF((G43-TRUNC(G43))<=0.333333217589825,0.333333217589825,(G43-TRUNC(G43)))))

Now to calculate the TAT a simple logic case of if adjusted entry time is greater than exit time use entry time instead

here is the formula =IF(J43>H43,H43-G43,H43-J43)


G43 = entry time
H43 = exit time
J43 = adjusted entry time
0.708332696762227 = time value for 5pm
0.333333217589825 = time value for 8am

Cheers!
Kodako!