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 hour...
Excel - formulas data worksheet
Formulas & Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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
      Diamond Contributor
      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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 08, 2021

      tyrelmiranda 

      NETWORKDAYS.INTL() allows to define holidays as 4th parameter.

  • nishanth1991's avatar
    nishanth1991
    Copper Contributor
    Aug 08, 2019

    SergeiBaklan 

     

    Could you please help me out to calculate TAT between particular time Windows. 

     

    Shift Start Time ESTShift End Time EST
    Sunday 8:30 PMMonday 5:30 AM
    Monday 8:00 AMMonday 5:00 PM
      
    Monday 8:30 PMTuesday 5:30 AM
    Tuesday 8:00 AMTuesday 5:00 PM
      
    Tuesday 8:30 PMWednesday 5:30 AM
    Wednesday 8:00 AMWednesday 5:00 PM
      
    Wednesday 8:30 PMThursday 5:30 AM
    Thursday 8:00 AMThursday 5:00 PM
      
    Thursday 8:30 PMFriday 5:30 AM
    Friday 8:00 AMFriday 5:00 PM

     

     

    Order_DateCompleted_DateManual TAT (Hours)TAT
    7/26/19 4:30 AM7/29/19 9:15 PM28:45 
    7/26/19 3:46 PM7/30/19 3:46 PM36:00 
    7/26/19 9:05 PM7/30/19 9:05 PM37:05 
    7/29/19 3:18 PM7/29/19 11:00 PM4:12 

Resources