Compare dates in Excel

[HungryHippos]

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

 

[HungryHippos]

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