I Need a formula to calculate the Turn Around Time

ahasan465231

New Member
Joined
Aug 10, 2022
Messages
7
Office Version
  1. 365
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
One nasty formula in B1, and the whole thing spread out below to maybe be more understandable.

Since the start time is before working hours, it's assumed to be 8:00am. If closing is after business hours, the thing is going to break, but I presume that doesn't happen.

Cell Formulas
RangeFormula
B1B1=INT(((INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))-(INT(A1)+(MAX(MOD(A1,1)-(1/3),0)*(24/16)))))+MOD(((INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))-(INT(A1)+(MAX(MOD(A1,1)-(1/3),0)*(24/16)))),1)/(24/16)
A4:A5A4=INT(A1)
B4:B5B4=MOD(A1,1)
C4:C5C4=(MAX(B4-(1/3),0)*(24/16))
D4:D5D4=(A4+C4)
E5E5=(D5-D4)
F5F5=MOD(E5,1)
G5G5=F5/(24/16)
H5H5=INT(E5)+G5
 
Upvote 0
One nasty formula in B1, and the whole thing spread out below to maybe be more understandable.

Since the start time is before working hours, it's assumed to be 8:00am. If closing is after business hours, the thing is going to break, but I presume that doesn't happen.

Cell Formulas
RangeFormula
B1B1=INT(((INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))-(INT(A1)+(MAX(MOD(A1,1)-(1/3),0)*(24/16)))))+MOD(((INT(A2)+(MAX(MOD(A2,1)-(1/3),0)*(24/16)))-(INT(A1)+(MAX(MOD(A1,1)-(1/3),0)*(24/16)))),1)/(24/16)
A4:A5A4=INT(A1)
B4:B5B4=MOD(A1,1)
C4:C5C4=(MAX(B4-(1/3),0)*(24/16))
D4:D5D4=(A4+C4)
E5E5=(D5-D4)
F5F5=MOD(E5,1)
G5G5=F5/(24/16)
H5H5=INT(E5)+G5
It doesn't work, please find sample sheet and share formula accordingly.
 

Attachments

  • Test Case.PNG
    Test Case.PNG
    17.6 KB · Views: 8
Upvote 0
Hello

Try this... make sure date format is correctly used...

Book1.xlsx
ABCDEFG
1OpenClosedNon Work HoursTime to SubtractResolve Time
2StartEndHours
32022/05/08 21:54:002022/06/08 14:28:0012:00:0008:00:0004:00:005.166725 Days 12:34:00
42022/05/08 22:16:002022/06/08 01:37:0012:00:0008:00:0004:00:005.166724 Days 23:21:00
52022/05/08 22:24:002022/05/08 23:04:0012:00:0008:00:0004:00:000.000000 Days 00:40:00
62022/05/08 22:44:002022/06/08 19:07:0012:00:0008:00:0004:00:005.166725 Days 16:23:00
72022/05/08 22:44:002022/06/08 13:30:0012:00:0008:00:0004:00:005.166725 Days 10:46:00
82022/05/08 23:28:002022/05/08 23:53:0012:00:0008:00:0004:00:000.000000 Days 00:25:00
92022/06/08 00:16:002022/06/08 00:40:0012:00:0008:00:0004:00:000.000000 Days 00:24:00
102022/06/08 00:55:002022/06/08 01:33:0012:00:0008:00:0004:00:000.000000 Days 00:38:00
112022/06/08 01:42:002022/06/08 21:57:0012:00:0008:00:0004:00:000.000000 Days 20:15:00
122022/06/08 05:42:002022/06/08 12:02:0012:00:0008:00:0004:00:000.000000 Days 06:20:00
Sheet1
Cell Formulas
RangeFormula
E3:E12E3=C3-D3
F3:F12F3=DAYS(B3,A3)*E3
G3:G12G3=TEXT(B3-A3-F3,"dd ""Days"" hh:mm:ss")
 
Upvote 0
No dear, it is also notworking.
actually we are giving services 24X7, 365 days. in this case we didn't have any weekend or holiday, so please is there anyone who guided me how to calculate TAT (Turn around Time) including weekends, while using excel.
 
Upvote 0
Non Business hours: 00:00 AM (Midnight) to 08:00 AM
In your original post you stated that non business hours are from midnight to 08:00... should this time be included in the TAT or be subtracted from it? Are those times when people are not working. According to below you want for all hours? Am I correct?
No dear, it is also notworking.
actually we are giving services 24X7, 365 days. in this case we didn't have any weekend or holiday, so please is there anyone who guided me how to calculate TAT (Turn around Time) including weekends, while using excel.
 
Upvote 0
In your original post you stated that non business hours are from midnight to 08:00... should this time be included in the TAT or be subtracted from it? Are those times when people are not working. According to below you want for all hours? Am I correct?
Dear Jimmypop,

actually I am using below formula, but it exclude weekends, my requirement is add weekdays in my working days, please suggest formula accordingly and thanks for your prompt responce.

=(NETWORKDAYS(A2,B2)-11)*("23:59"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,11),"23:59","8:00"),"23:59")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,11),"23:59","8:00")
 
Upvote 0
We need to know if we need to exclude the hours from 23:59 to 08:00 and then include all days between the two dates? Looking at formula above this does seem to be the case... Also do you want the total days, hours and minutes it took?

Maybe someone else reading has a suggestion?
 
Upvote 0
What about the below...

Book1.xlsx
ABC
1OpenClosedResolve Time
22022/05/08 21:542022/06/08 14:2808:03:00
32022/05/08 22:162022/06/08 01:3718:50:00
42022/05/08 22:242022/05/08 23:0400:40:00
52022/05/08 22:442022/06/08 19:0711:52:00
62022/05/08 22:442022/06/08 13:3006:15:00
72022/05/08 23:282022/05/08 23:5300:25:00
82022/06/08 00:162022/06/08 00:4000:24:00
92022/06/08 00:552022/06/08 01:3300:38:00
102022/06/08 01:422022/06/08 21:5720:15:00
112022/06/08 05:422022/06/08 12:0206:20:00
Sheet1 (2)
Cell Formulas
RangeFormula
C2:C11C2=$B2-$A2 - (INT($B2)-INT($A2))*(1+TIME(8,0,0)-TIME(23,59,0))
 
Upvote 0
We need to know if we need to exclude the hours from 23:59 to 08:00 and then include all days between the two dates? Looking at formula above this does seem to be the case... Also do you want the total days, hours and minutes it took?

Maybe someone else reading has a suggestion?
Dear Jimmypop,

in my previous message, I wrongly mentioned weekdays instead of weekends, so, please guide accordingly.

actually I am using below formula, but it exclude weekends, my requirement is add weekends in my working days, please suggest formula accordingly and thanks for your prompt response.

=(NETWORKDAYS(A2,B2)-11)*("23:59"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,11),"23:59","8:00"),"23:59")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,11),"23:59","8:00")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top