Solved
Excel Date and Time formula
Posted on 2012-03-23
I have a spreadsheet with 2 fields, with date and time. See below
Severity Closed.TIME Re-Assigned.TIME
Sev 3 2/2/2012 11:41 1/31/2012 16:09
Work Order 2/13/2012 10:07 12/22/2011 15:46
Work Order 3/5/2012 11:14 1/9/2012 18:12
Work Order 2/14/2012 11:28 1/18/2012 9:10
Sev 3 2/13/2012 12:13 2/6/2012 9:51
Sev 3 2/1/2012 7:46 1/30/2012 9:11
Sev 3 2/1/2012 14:46 1/31/2012 16:32
Sev 4 2/2/2012 17:11 1/30/2012 17:43
I want to calculate the total time it took to close the ticket. I assume I am going to create a formula using the 2 fields(re-assigned and closed)? I then want to look at the first column and see what severity it is, and compare the time to another table (See below) If I am within the time frame, I pass SLA, if not I fail. For example if the total time is less the 24 hours and Column A has Sev3, then I want the formula to say "pass" if its over 24 hours, it should say "fail". Any assistance would be greatly appreciated!
Time Table:
Sev1 4 hours
Sev2 8 hours
sev3 24 hours
sev4 48 hours