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.

 

[Mustafa Evans]

That's the thing, both, but i realise now that this can't happen. If G is Urgent I&J cell should be grey, but i need a way of showing dates that have expired aswell.

 

[Fluff]

You have a choice, grey cells or red/amber cells, unless you change one of them to font colour & one to be fill colour.

 

[Mustafa Evans]

You have a choice, grey cells or red/amber cells, unless you change one of them to font colour & one to be fill colour.

I really don't mind. OK Can we keep cell GREY, and do something to show dates expiries. Genuinely grateful for your assistance.

 

[Fluff]

How about

+Fluff v2.xlsm
	F	G	H	I	J
8
9		Urgent	15/10/2020	01/11/2020
10
11		Routine	15/10/2020	01/11/2020
12
13
14
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I8:J500	Expression	=$G8="Urgent"	text	NO
H8:H500	Expression	=$G8="Routine"	text	NO
H8:J500	Expression	=AND(H8<>"",H8<TODAY())	text	NO
H8:J500	Expression	=AND(H8<>"",H8<=TODAY()+5)	text	NO

 

[Mustafa Evans]

Send me you address for the biggest choc box ! It all works, Thanks so very much.

 

[Fluff]

You're welcome & thanks for the feedback.