Using Conditional Formatting with expired dates

[Mustafa Evans]

Hi all, hope you can help am attempting to use conditional formatting to show expired and near expired (5 days) dates in my spreadsheet.

For expired dated i would like those cells to be RED.
For dates about to expire in the next 5 days those cells should be AMBER
and finally, those cells that are not about to expire should remain WHITE.

Am trying to use the TODAY() function in my calculations. Hope you can help.

The dates are in columns H, I and J, but begin in cell H8.

RED - =H8<=TODAY()
AMBER - =H8>=TODAY()-5
WHITE - =H8>TODAY()

Having done this am unsure what order they should run. Or is there a easier way.

Many thanks.

 

[Fluff]

I would change the amber to

H8<=Today()+5

and get rid of the rule for white.

 

[Mustafa Evans]

Hi, sorry your suggestion doesn't seem to work, could you have another look at it please.

Many thanks.

 

[Fluff]

You need to remove the quotes from the amber formula.

 

[Mustafa Evans]

Thanks, i thought i have deleted those already !!. I works now except i had to re-order the order they work with RED first followed by AMBER.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Mustafa Evans]

My protocol in CF is now working for dates, but i have another element regarding whether an appointment is Urgent or Routine. I've also managed to get this working. BUT when i combine them the conditional formatting clash and the result isn't what i need. I wonder if you could help further please.

Am not sure why the formula works as seems very untidy. The date element is OK, i just need to combine now.

So, the appointment type (Routine or Urgent) is selected in column G:

If ROUTINE is selected in cell G8, cell H8 is GREYED out (and ideally that cell is locked)
If URGENT, is selected in cell G8, then H8 will accept a date (entered manually) and cells I8 and J8 and GREYED out (and ideally those cells locks)

 

[Fluff]

You cannot lock cells with conditional formatting.

Those rules seem to be working, so what's the problem.

 

[Mustafa Evans]

The Grey CF rules work, and in a different spreadsheet the date rules work, but they don't work when put together in the same spreadsheet.

 

[Fluff]

If G is Urgent should I & J still show red/amber, or should they remain grey?