Conditional Formatting based on IF statement

[currymonster]

Hi I wonder if anybody could help? I know a little Excel but not a lot
In the attached workbook I’m trying to apply conditional formatting to column I based on one of three criteria, I’d like the cells to display the value but also change colour based on the value.
Colum I is calculated field based on Subtracting Time Query Actioned (Colum F) from Time Email Received (Colum) E and then colour the cell in column I (Response Time) either Green, Yellow, Red dependent on the value. The cells have been formatted as Time to subtract one time from another.
The conditions in time values are:
• 0:00:00-00:15:00 colour the cell green
• 00:15:01-30:00:00 colour the cell yellow
• Anything greater than 30:00:01 colour the cell red
So with the dummy data and using the above criteria
• I2 should display 0:10 and be green
• I3 should display 0:46 and be red
• I4 should display 0:30 and be yellow

The IF Statement I’m using (copied into cell K2) I tive tried using that and then adding a rule conditional formatting to change the cell colour but it doesn’t work and I can’t figure it out, can anybody help?
Thanks for taking the time to read and any responses.

[HansV]

Hi,

The formula in I2 subtracts Time Email Received from Time Query Resolved, but the formulas in I3 and I4 subtract Time Email Received from Time Query Actioned. Wouldn't it be better to use a single formula, for example

=IF([@[Time Query Resolved]]="",[@[Time Query Actioned]],[@[Time Query Resolved]])-[@[Time Email Received]]

or even better

=IF([@[Time Query Actioned]]="","",IF([@[Time Query Resolved]]="",[@[Time Query Actioned]],[@[Time Query Resolved]])-[@[Time Email Received]])

Note that I didn't use the TEXT function - it's better to work with time values than with text values in calculations. You can set the number format of column I to h:mm.

See the attached version for the conditional formatting rules that I created.

System Response Times.xlsx

[currymonster]

Thank you Hans, that great and I've learnt something as well :