How to calculate Turn Around Time (TAT) considering weekend and working Hours..!

%3CLINGO-SUB%20id%3D%22lingo-sub-819762%22%20slang%3D%22en-US%22%3EHow%20to%20calculate%20Turn%20Around%20Time%20(TAT)%20considering%20weekend%20and%20working%20Hours..!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819762%22%20slang%3D%22en-US%22%3E%3CP%3EConsidering%20Two%20Set%20of%20Data%3C%2FP%3E%3CP%3E%3CSTRONG%3EStart%20Date%20%22A1%3C%2FSTRONG%3E%3D22-08-2019%2011%3A55%20AM%22%3C%2FP%3E%3CP%3E%3CSTRONG%3EEnd%20Date%3A-%20%22B2%3C%2FSTRONG%3E%3D27-08-2019%205%3A00%20PM%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EConditions%3A-%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EOffice%20working%20Hours%20is%2010%20AM%20-%207%20PM%26nbsp%3B%3C%2FLI%3E%3CLI%3ELunch%20is%20between%201%20PM-%202%20PM%3C%2FLI%3E%3CLI%3ESaturday%20Sunday%20fixed%20off%3C%2FLI%3E%3CLI%3EAnd%20a%20list%20of%20holiday%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3ETo%20Find%3A-%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3BI%20want%20to%20find%20the%20number%20of%20hours%20it%20took%20to%20complete%20a%20Task%20considering%20working%20Hours..%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834844%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20calculate%20Turn%20Around%20Time%20(TAT)%20considering%20weekend%20and%20working%20Hours..!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397301%22%20target%3D%22_blank%22%3E%40Ateeb%3C%2FA%3E%26nbsp%3BYou've%20posted%20your%20question%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ECommunity%20Discussion%20space%3C%2FA%3E%2C%20which%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20indicate%20which%20product%20your%20question%20is%20regarding%3F%20I'll%20be%20happy%20to%20suggest%20a%20space%20to%20post%20in%20and%20move%20your%20question%20there.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842612%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20calculate%20Turn%20Around%20Time%20(TAT)%20considering%20weekend%20and%20working%20Hours..!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397301%22%20target%3D%22_blank%22%3E%40Ateeb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20that's%20Excel%20and%20assuming%20Start%2FEnd%20could%20be%20outside%20the%20working%20hours%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20761px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130604i979A8689CDE60353%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%3Eformula%20in%20C2%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(INT(B2)%26gt%3BINT(A2)%2C%0A%20%20NETWORKDAYS.INTL(A2%2B1%2CB2-1%2C%220000011%22%2C%24J%242%3A%24J%243)*8%2F24%20%2B%0A%20%20(WORKDAY.INTL(A2-1%2C1%2C%220000011%22%2C%24J%242%3A%24J%243)%3DINT(A2))*%0A%20%20(%0A%20%20%20%20%24F%242-MEDIAN(%24E%242%2C%24F%242%2CMOD(A2%2C1))%20%2B%0A%20%20%20%20%24H%242-MEDIAN(%24G%242%2C%24H%242%2CMOD(A2%2C1))%0A%20%20)%20%2B%0A%20%20(WORKDAY.INTL(B2-1%2C1%2C%220000011%22%2C%24J%242%3A%24J%243)%3DINT(B2))*%0A%20%20(%0A%20%20%20%20MEDIAN(%24E%242%2C%24F%242%2CMOD(B2%2C1))-%24E%242%20%2B%0A%20%20%20%20MEDIAN(%24G%242%2C%24H%242%2CMOD(B2%2C1))-%24G%242%0A%20%20)%2C%0A%20%20MIN(%24F%242%2CMOD(B2%2C1))-MEDIAN(%24E%242%2C%24F%242%2CMOD(A2%2C1))%2B%0A%20%20MEDIAN(%24G%242%2C%24H%242%2CMOD(B2%2C1))-MAX(%24G%242%2CMOD(A2%2C1))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EResult%20formatted%20as%20%5Bhh%5D%3Amm.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirtst%20we%20check%20if%20Start%2FEnd%20dates%20are%20different%20days%2C%20if%20so%20calculate%20number%20of%20full%20workdays%20in%20between%20plus%20time%20for%20each%20of%20Start%2FEnd%20if%20only%20they%20are%20workdays.%20Finally%20calculate%20the%20difference%20if%20Start%2FEnd%20is%20the%20same%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Considering Two Set of Data

Start Date "A1=22-08-2019 11:55 AM"

End Date:- "B2=27-08-2019 5:00 PM"

 

Conditions:- 

  • Office working Hours is 10 AM - 7 PM 
  • Lunch is between 1 PM- 2 PM
  • Saturday Sunday fixed off
  • And a list of holiday

To Find:-

 I want to find the number of hours it took to complete a Task considering working Hours.. 

 

Please suggest. 

2 Replies

@Ateeb You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. 

 

Can you indicate which product your question is regarding? I'll be happy to suggest a space to post in and move your question there.

@Ateeb 

Assuming that's Excel and assuming Start/End could be outside the working hours, for such sample

image.png

formula in C2 could be

=IF(INT(B2)>INT(A2),
  NETWORKDAYS.INTL(A2+1,B2-1,"0000011",$J$2:$J$3)*8/24 +
  (WORKDAY.INTL(A2-1,1,"0000011",$J$2:$J$3)=INT(A2))*
  (
    $F$2-MEDIAN($E$2,$F$2,MOD(A2,1)) +
    $H$2-MEDIAN($G$2,$H$2,MOD(A2,1))
  ) +
  (WORKDAY.INTL(B2-1,1,"0000011",$J$2:$J$3)=INT(B2))*
  (
    MEDIAN($E$2,$F$2,MOD(B2,1))-$E$2 +
    MEDIAN($G$2,$H$2,MOD(B2,1))-$G$2
  ),
  MIN($F$2,MOD(B2,1))-MEDIAN($E$2,$F$2,MOD(A2,1))+
  MEDIAN($G$2,$H$2,MOD(B2,1))-MAX($G$2,MOD(A2,1))
)

Result formatted as [hh]:mm.

 

Firtst we check if Start/End dates are different days, if so calculate number of full workdays in between plus time for each of Start/End if only they are workdays. Finally calculate the difference if Start/End is the same date.