# Thread: Conditional Formatting for dates

1. ## Conditional Formatting for dates

Hi there, I have set up a spreadsheet showing when our staff completed certain training courses. Some courses are only valid for 1 year, some 3 years, some 5 years etc. I would like to use conditional formatting to highlight if the dates are coming up to expiry (within 4 months of today goes yellow), are very near expiry (within 2 months of today goes orange) or have expired (goes red), and also to ignore if there is no date, as not all courses are relevant to all staff.

Would appreciate any help!

Thanks

2. ## highlight expiry date

Originally Posted by sarahbravo
and also to ignore if there is no date, as not all courses are relevant to all staff.
Try formulas
Red
Code:
`=IF(AND(ISNUMBER(E5);E5<=TODAY());TRUE;FALSE)`
Orange
Code:
`=IF(AND(ISNUMBER(E5);E5-90<=TODAY());TRUE;FALSE)`
Yellow
Code:
`=IF(AND(ISNUMBER(E5);E5-150<=TODAY());TRUE;FALSE)`

3. Originally Posted by navic
Try formulas
Red
Code:
`=IF(AND(ISNUMBER(E5);E5<=TODAY());TRUE;FALSE)`
Orange
Code:
`=IF(AND(ISNUMBER(E5);E5-90<=TODAY());TRUE;FALSE)`
Yellow
Code:
`=IF(AND(ISNUMBER(E5);E5-150<=TODAY());TRUE;FALSE)`
-----------------------------------------------------------------------
This is good, except that the OP's dates are not the expiration dates!
I'd suggest the OP use EXPIRATION date, rather than TESTING date. If he does, the above procedure should work.

Hope this helps!

4. @candybg

@sarah

what are those dates? When is the expiration date ? PLease be more specific

@ navic

CF does not need the IF function nor the TRUE/FALSE usually. A condition like

=AND(ISNUMBER(E5);E5-90<=TODAY()) is perfectly valid ( I'm not saying it is the correct answer to the OP's query, though)

5. Originally Posted by Pecoflyer
@ navic
CF does not need the IF function nor the TRUE/FALSE usually.
I know that.
But! @sarahbravo will better understand the essence how work of formula.

here is my example for @sarahbravo