Conditional formatting help

kyles1981au

New Member
Joined
Jan 3, 2022
Messages
2
Platform
  1. Windows
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!
 

Attachments

  • example dataset image.png
    example dataset image.png
    15.5 KB · Views: 8

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,523
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:

Excel Formula:
=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

New Member
Joined
Jan 3, 2022
Messages
2
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,429
Messages
5,929,842
Members
436,698
Latest member
darshanw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top