I Need a formula to calculate the Turn Around Time

[ahasan465231]

Ticket Open Date & Time: 8/6/2022 3:27:00 AM
Ticket Close Date & Time: 8/8/2022 3:57:32 PM
Business Hours: 08:00 AM to 00:00 AM (Midnight)
Non Business hours: 00:00 AM (Midnight) to 08:00 AM
Weekdays : Monday to Sunday
Weekends: no weekends & holidays

 

[Jimmypop]

Ok then have you tried @JamesCanale suggestion in post #2 with the formula he suggested? Results are below:

Book1.xlsx
	A	B	C
1	Open	Closed	Resolve Time
2	8/5/2022 21:54	8/6/2022 14:28	08:34
3	8/5/2022 22:16	8/6/2022 1:37	01:44
4	8/5/2022 22:24	8/5/2022 23:04	00:40
5	8/5/2022 22:44	8/6/2022 19:07	12:23
6	8/5/2022 22:44	8/6/2022 13:30	06:46
7	8/5/2022 23:28	8/5/2022 23:53	00:25
8	8/6/2022 0:16	8/6/2022 0:40	00:00
9	8/6/2022 0:55	8/6/2022 1:33	00:00
10	8/6/2022 1:42	8/6/2022 21:57	13:57
11	8/6/2022 5:42	8/6/2022 12:02	04:02
Sheet1 (4)

Cell Formulas
Range	Formula
C2:C11	C2	=INT(((INT(B2)+(MAX(MOD(B2,1)-(1/3),0)*(24/16)))-(INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))))+MOD(((INT(B2)+(MAX(MOD(B2,1)-(1/3),0)*(24/16)))-(INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))),1)/(24/16)

 

[ahasan465231]

Dear All, this is just for your info, below formula really works and full fill my requirement,

=IF((INT(B2)=INT(A2)),(MEDIAN(MOD(B2,1),"24:00","08:00")-MEDIAN(MOD(A2,1),"24:00","08:00")),((INT(B2)-INT(A2))-0)*("24:00"-"08:00")+MEDIAN(MOD(B2,1),"24:00","08:00")-MEDIAN(MOD(A2,1),"24:00","08:00"))

 

[Jimmypop]

=IF((INT(B2)=INT(A2)),(MEDIAN(MOD(B2,1),"24:00","08:00")-MEDIAN(MOD(A2,1),"24:00","08:00")),((INT(B2)-INT(A2))-0)*("24:00"-"08:00")+MEDIAN(MOD(B2,1),"24:00","08:00")-MEDIAN(MOD(A2,1),"24:00","08:00"))

Thanks for letting us know... I also tested the formula in post #11 and the results are exactly the same... could you maybe tell us how you got to your formula?

The formula was from @JamesCanale I just put it to test in your table requirement.

=INT(((INT(B2)+(MAX(MOD(B2,1)-(1/3),0)*(24/16)))-(INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))))+MOD(((INT(B2)+(MAX(MOD(B2,1)-(1/3),0)*(24/16)))-(INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))),1)/(24/16)

 

[ahasan465231]

Thanks for letting us know... I also tested the formula in post #11 and the results are exactly the same... could you maybe tell us how you got to your formula?

The formula was from @JamesCanale I just put it to test in your table requirement.

=INT(((INT(B2)+(MAX(MOD(B2,1)-(1/3),0)*(24/16)))-(INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))))+MOD(((INT(B2)+(MAX(MOD(B2,1)-(1/3),0)*(24/16)))-(INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))),1)/(24/16)

Dear,
@JamesCanale formula doesn't work, when resolution total time in exceeding 24 hours, that formula add 8 hours per day, which you may review in attached snap shot, but as per my formula it gives the exact time (after excluding non business hours (00:00:00 to 08:00:00)).