Conditional formatting help

[kyles1981au]

Hi,
I have a data set which includes a 'triage category' (column D, general/number format) and 'wait to be seen' time (column I, time format (h:mm).
I'm trying to set up conditional formatting so that:
if triage category = 4
AND
wait time >1 hour
then cell will be red,

Then, a second format for if triage category = 5 AND waiting time > 2 hours, the cell will also be red.
If possible (and not making things too complicated) then also to have cells that don't meet this criteria to be green (ie. triage 4 waits <1hr, triage 5 waits <2hrs).

I'm having trouble finding the right formula to combine the IF statement for triage category with the 'greater than' for wait time.

Possible complication - the data in the 'wait time column' is a formula and not a directly entered value. The formula for this column is =F2-E2 (which is a simple subtraction formula using the data entered for the time the patient was seen by a staff member (in h:mm) minus the arrival time (in h:mm).

Any help for the right formula would be much appreciated.

Wasn't able to download the file needed to upload a dataset as I am on a work computer. If needed, I can try to do this when I get home.

Thanks again!

 

[6StringJazzer]

Format the cells as green. If the red CF rule doesn't apply then they will simply default to green.

Your red CF formula is:

=OR(AND(D1=4,I1>TIME(1,0,0)),AND(D1=5,I1>TIME(2,0,0)))

You didn't say which cells you want to format.

 

[kyles1981au]

Thanks heaps!!! I couldn't remember how to manage times.
Worked like a charm.

With regard to highlighting I've been able to do it highlighting the whole row as well as just highlighting the cell in question so I'll just play around with it for a bit and see which I prefer.

Thanks again for the quick and super helpful response.