Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-171029%22%20slang%3D%22en-US%22%3EFormula%20for%20Trun%20Around%20Time%20(TAT)%20For%20incident%20No%20Weekend%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171029%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20need%20formula%20for%20Trun%20around%20Time%20(TAT)%20For%20Incident.%3C%2FP%3E%0A%3CP%3EFor%20Ex.%20Start%20Date%20Time%20%3A%2025%2F2%2F2018%2010%3A00%20AM%3C%2FP%3E%0A%3CP%3EEnd%20time%20%3A%2028%2F2%2F2018%206%3A00%20PM%3C%2FP%3E%0A%3CP%3EManual%20TAT%20calculation%20is%20%3A-%2053%3A00%3C%2FP%3E%0A%3CP%3EWorking%20hours%20for%20Sat%20to%20thu%20is%207%3A00%20Am%20to%2010%20Pm%26nbsp%3B%20%26amp%3B%20For%20Friday%20is%209%3A30%20AM%20to%206%3A30%20PM%3C%2FP%3E%0A%3CP%3ESo%20I%20need%20the%20Formula%20as%20per%20the%20Working%20hours%20and%20all%20days%20which%20calculate%20TAT.%3C%2FP%3E%0A%3CP%3ESo%20please%20help%20me%20thank%20you%3C%2FP%3E%0A%3CP%3E%3CLI-WRAPPER%3E%3C%2FLI-WRAPPER%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-171029%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20-%20formulas%20data%20worksheet%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-171065%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20Trun%20Around%20Time%20(TAT)%20For%20incident%20No%20Weekend%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171065%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20data%20structured%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20597px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30265i53EFE248FD0E9218%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethe%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DNETWORKDAYS.INTL(F2%2B1%2CG2-1%2C%221111011%22)*9%2F24%20%2B%0A%20%20(INT(G2-1)-INT(F2%2B1)%2B1-NETWORKDAYS.INTL(F2%2B1%2CG2-1%2C%221111011%22))*15%2F24%20%2B%0A%20(IF(WEEKDAY(F2%2C2)%3D5%2C%24C%243%2C%24C%242)-MOD(F2%2C1)%20%2B%0A%20MOD(G2%2C1)-IF(WEEKDAY(G2%2C2)%3D5%2C%24B%243%2C%24B%242))%3C%2FPRE%3E%0A%3CP%3EI%20added%20helper%20cells%20for%20working%20hours%2C%20in%20general%20could%20be%20hardcoded%20using%20TIME()%20function.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESample%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795854%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20Trun%20Around%20Time%20(TAT)%20For%20incident%20No%20Weekend%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20out%20to%20calculate%20TAT%20between%20particular%20time%20Windows.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EShift%20Start%20Time%20EST%3C%2FTD%3E%3CTD%3EShift%20End%20Time%20EST%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESunday%208%3A30%20PM%3C%2FTD%3E%3CTD%3EMonday%205%3A30%20AM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMonday%208%3A00%20AM%3C%2FTD%3E%3CTD%3EMonday%205%3A00%20PM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMonday%208%3A30%20PM%3C%2FTD%3E%3CTD%3ETuesday%205%3A30%20AM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETuesday%208%3A00%20AM%3C%2FTD%3E%3CTD%3ETuesday%205%3A00%20PM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETuesday%208%3A30%20PM%3C%2FTD%3E%3CTD%3EWednesday%205%3A30%20AM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWednesday%208%3A00%20AM%3C%2FTD%3E%3CTD%3EWednesday%205%3A00%20PM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWednesday%208%3A30%20PM%3C%2FTD%3E%3CTD%3EThursday%205%3A30%20AM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThursday%208%3A00%20AM%3C%2FTD%3E%3CTD%3EThursday%205%3A00%20PM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThursday%208%3A30%20PM%3C%2FTD%3E%3CTD%3EFriday%205%3A30%20AM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFriday%208%3A00%20AM%3C%2FTD%3E%3CTD%3EFriday%205%3A00%20PM%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EOrder_Date%3C%2FTD%3E%3CTD%3ECompleted_Date%3C%2FTD%3E%3CTD%3EManual%20TAT%20(Hours)%3C%2FTD%3E%3CTD%3ETAT%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F26%2F19%204%3A30%20AM%3C%2FTD%3E%3CTD%3E7%2F29%2F19%209%3A15%20PM%3C%2FTD%3E%3CTD%3E28%3A45%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F26%2F19%203%3A46%20PM%3C%2FTD%3E%3CTD%3E7%2F30%2F19%203%3A46%20PM%3C%2FTD%3E%3CTD%3E36%3A00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F26%2F19%209%3A05%20PM%3C%2FTD%3E%3CTD%3E7%2F30%2F19%209%3A05%20PM%3C%2FTD%3E%3CTD%3E37%3A05%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F29%2F19%203%3A18%20PM%3C%2FTD%3E%3CTD%3E7%2F29%2F19%2011%3A00%20PM%3C%2FTD%3E%3CTD%3E4%3A12%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
Abhishek Shrivastav
Occasional Visitor

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

 

 

2 Replies

Hi,

 

With data structured like this

image.png

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.

@Sergei Baklan 

 

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