Compare dates in Excel

HungryHippos · 8 May 2009 at 11:33

Hi everyone

I'm trying to compare date values in excel but i'm having some problems getting it do what I want, here is the scenario:

I have an excel spreadsheet that contains a number of support cases that have been closed, the table has the following values:

Case Number
Title
Created Date (DD/MM/YYYY)
Closed Date (DD/MM/YYYY)
Closed By (Name)
SLA - Using VLOOKUP I get the SLA of the case based upon the severity, this is formatted in dd:hh:mm
Severity (1-4, 1 being higher severity)

If I subtract the Created Date from the Closed Date I get a value that gives me the difference between the two, I have successfully formatted this into a dd:hh:mm format using custom data formatting, I call this column "Time to Close"

What I then need to do is compare the "Time to Close" with the SLA to find out if the case was closed within the specified SLA for the row, I need to either do this using Conditional Formatting (preffered) to colour code the cells or to have a seperate column that compares the two.

The thing that's getting me a little stuck is that when I compare the Time to Close with the SLA using an IF statement it doesn't seem to compare the values properly.

For example, in one cell I have a Time to Close of 07:22:19 and an SLA of 02:00:00. My formula says =IF(E3>F3, "Out of SLA", "In SLA") however it doesn't seem to pick up that anything is out of SLA even if E3 is > F3. If I manually type the same value into E3 rather than use the formula it *does* work!

Thanks for any help you can give

Dolph · 8 May 2009 at 11:41

how are you calculating the difference between the two dates? This is likely to be the source of the problem.

look at this function for the calculation.

http://www.cpearson.com/excel/datedif.aspx

or this approach if you are comfortable with vba

http://www.techonthenet.com/excel/formulas/datediff.php

HungryHippos · 8 May 2009 at 11:49

Think I figured out where it was going wrong but i'm still not done

To simplify it a liittle it looks like this:

C D E F
Created On | Closed On | Time to Close | SLA
dd/mm/yyyy | dd/mm/yyyy | =D2-C2 | dd:hh:mm

So on an so forth, this works OK in that in Column E if the format is set to General it gives me a decimal value, I can then change to a custom date format and it knows what it is, the problem seems to be with the SLA field, because I literally entered it in as the dd:hh:mm format when I change the column to general it doesn't give it a decimal value.

I think in order to resolve this I need to know how to convert time to decimal so I can use decimal values instead, any ideas?

the problem seems to be in

Ciphon · 8 May 2009 at 12:17

Could you not just subtract the Time to Close from the SLA and then do a find on all negatives and colour code that way?

So:

SLA Sev 1 (1 Hour) - Time to Close (1 Hour 3 Minutes) = -3 Minutes?

PS - Siri knows a lot about Excel I think

Dolph · 8 May 2009 at 12:19

The problem is that you're trying to work in two different formats.

If it was me, I'd use datedif to get the number of days it took to close, and present the SLA as a number of days as well, which would then put the two in the same format.

If you want to keep SLA as a date, then you can do it, but it'll get a little more complicated because you need to return time to close as a date, which does't make a lot of sense.