Calculate Turnaround Time/Group

[ramesh_cl1981]

Hi Friends,

I have 2 fields dateSubmit and dateComplete on which I need to calculate turnaround time in hours, submit and complete dates does not have any time restrictions [can be submitted/completed 24x7]. The calculated TAT should then be categorized as in the attached sheet.

Please help me with this.

[BadlySpelledBuoy]

D2:
=C2-B2 and formatted as [hh]:mm
I2:
=COUNTIFS($A:$A,$H2,$D:$D,"<1")
J2:
=COUNTIFS($A:$A,$H2,$D:$D,">=1",$D:$D,"<1.5")
K2:
=COUNTIFS($A:$A,$H2,$D:$D,">=1.5",$D:$D,"<2")
L2:
=COUNTIFS($A:$A,$H2,$D:$D,">=2")
M2:
=SUM(I2:L2)

Enter those formulas in the cells mentioned and copy down.

BSB

[BadlySpelledBuoy]

See attached for the above in place.

BSB

[FlameRetired]

With two helper rows above the output range for the lower and upper limits this formula in the first cell filled down and across.

Please Login or Register  to view this content.

[shukla.ankur281190]

@Dave May I ask you one question why did you divide the values when countifs can simply retrieve the results ?

[ramesh_cl1981]

Thank you so much BSB, exactly what I wanted!!!

[BadlySpelledBuoy]

Glad I could help

BSB

[FlameRetired]

@Dave May I ask you one question why did you divide the values when countifs can simply retrieve the results ?

OP's upload showed blanks for desired output. Not reciprocating would output 0s. It was to coerce those 0s into errors and then wrap an IFERROR around that.