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