Hi Need Some Urgent Help from the Experts.

[Jeva]

Hi I"m having a Difficulty to figure out formula . I'm trying to calculate the Resolution Hours Taken to Complete The Ticket, Min Cycle Time & Max Cycle Time. Is there anyone can help me on this? Need a help urgently from Experts.

Below are the Breakdown of the Data in the Each cell.
02 : Create Date (11/23/2011 15:00)
P2 : Resolved Date 12/1/2011 16:00
AB2 : Start Shift (7:00 AM)
AC2: End Shift (6:00 PM)
AA2 : Resolution Hours (Need Formula) Format (HH:MM:SS)
AD2: Max Cycle Time (Need Formula)
AE2 : Min Cycle Time (Need Formula)

Things to Exclude.
Weekends & Non Business Hours


I having difficulties to figure out the Calculation to Get the total Hours Taken To Complete.

[Don]

Hi there!

For the hours, try a formula like this:

Code: Select all

=(A2-A1)*24

where A2 is the cell with the most recent time/date and A1 is the cell with the earlier time/date. For the cycle time, you could do a similar thing using the networkdays() function.

Is this what you were looking for?