Forum Discussion

Abhishek Shrivastav's avatar
Abhishek Shrivastav
Copper Contributor
Mar 13, 2018

Formula for Trun Around Time (TAT) For incident No Weekend

Hi All,

 

I need formula for Trun around Time (TAT) For Incident.

For Ex. Start Date Time : 25/2/2018 10:00 AM

End time : 28/2/2018 6:00 PM

Manual TAT calculation is :- 53:00

Working hours for Sat to thu is 7:00 Am to 10 Pm  & For Friday is 9:30 AM to 6:30 PM

So I need the Formula as per the Working hours and all days which calculate TAT.

So please help me thank you

 

 

Excel - formulas data worksheet
Formulas & Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 13, 2018

    Hi,

     

    With data structured like this

    the formula could be

    =NETWORKDAYS.INTL(F2+1,G2-1,"1111011")*9/24 +
  (INT(G2-1)-INT(F2+1)+1-NETWORKDAYS.INTL(F2+1,G2-1,"1111011"))*15/24 +
 (IF(WEEKDAY(F2,2)=5,$C$3,$C$2)-MOD(F2,1) +
 MOD(G2,1)-IF(WEEKDAY(G2,2)=5,$B$3,$B$2))

    I added helper cells for working hours, in general could be hardcoded using TIME() function.

     

    Sample is attached.

    • vickyakhatri's avatar
      vickyakhatri
      Copper Contributor
      Nov 14, 2023

      SergeiBaklan I am working on a sheet where I need to calculate the turn-around time. I applied the network.intl formula and getting the number days we took for deliveries. 

       

      However, the number of days getting calculated is 2 days (and the enquiry days is 13th and we shared the resume on 14th). can you please help 

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Nov 15, 2023

        vickyakhatri 

        NETWORKDAYS.INTL counts both start and end days, you need to deduct one. Perhaps you may share the sample what exactly and how you calculate.

    • gs7488's avatar
      gs7488
      Copper Contributor
      Aug 06, 2021

      SergeiBaklan hi your formula has been incredibly useful! thank you . my only problem is when i use your formula for start time being the day where teh working shift is longer (12.5 hours) and the end time is the day of shorter hours (10.5 hours) my result is giving me 2 hours extra; example below 

       

      start time (working hours 12.5 hours this day- so 10 hours 15 mins until end of day)

      05/08/2021 08:15

       

      end time (start of day is 6am so 1 hour and 39 minutes to completion so total time should be 11 hours 54 minutes but im getting 13 hours 54 minutes)

      06/08/2021 07:39

       

       

      my formula 

      =NETWORKDAYS.INTL(L369+1,Y369-1,"1111011")*10.5/24+(INT(Y369-1)-INT(L369+1)+1-NETWORKDAYS.INTL(L369+1,Y369-1,"1111011"))*12.5/24 +(IF(WEEKDAY(L369,2)=5,Sheet2!$C$4,Sheet2!$C$3)-MOD(L369,1)+MOD(Y369,1)-IF(WEEKDAY(Y369,2)=5,Sheet2!$B$4,Sheet2!$B$3))

    • tyrelmiranda's avatar
      tyrelmiranda
      Copper Contributor
      Apr 08, 2021

      SergeiBaklan is it possible to exclude a list of national holidays in the calculation of TAT?

      E.g. 02/04/2021 - Good Friday

Resources