Calculate Turnaround Time/Group

[ram_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.

[NBVC]

For the TAT use:

=(C2-B2)*24

copied down. Format column As General (or Number).

Then in I2 you can use a formula like:

=COUNTIFS($A$2:$A$22,$H2,$D$2:$D$22,"<=24")

copied down

in J2, add another parameter:

=COUNTIFS($A$2:$A$22,$H2,$D$2:$D$22,">24",$D$2:$D$ 22,"<=36")

copied down

repeat structure in remaining columns.

[p45cal]

In the attached are two offerings, the first on Sheet1 very similar to NBVC's solution except without the x24. This gives results in days (although formatted to show hours and minutes with [hh]:mm formattting). So the CountIfs use days too. eg.:
=COUNTIFS($A$2:$A$22,$H3,$E$2:$E$22,">1.5",$E$2:$E $22,"<=2")

On Sheet2 is a pivot table solution, with an added column F to categorise each row into your 4 groups using index/match and a little table. The downside is that the pivot table won't show the groups if there are no members (there are no >24<36 or >48 in your data).