Conditional Formatting for dates

sarahbravo

New member
Joined
Oct 14, 2014
Messages
1
Reaction score
0
Points
0
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 :)
 

Attachments

  • Training Matrix - tester.xlsx
    42 KB · Views: 26
highlight expiry date

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)
 
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!
 
@candybg

Please don't quote entire posts. They clutter the thread and make it hard to read.

@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)
 
Last edited:
@ 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
 

Attachments

  • sarahbravo_Training Matrix - tester.xlsx
    39.3 KB · Views: 14
Last edited:
Back
Top