Conditional formatting for expiry dates

[North_Yorks]

Hi,

I have been given a training s/sheet to monitor the teams training schedule. I'm wanting it to automatically change colour (like a traffic light system) Amber a month before it expires, and then red if expired or blank (unless cell says not required). Then in date cells could be green. I have tried watching various videos but to no avail. Is there a simple method?

Thanks, Jen

[#2]

@North_Yorks 

If I may recommend, you can use conditional formatting to do this.

See the attached file for examples or click in the upper link for more informations.

Hope I could help you with these information.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@North_Yorks  So i created 2 rules:

In row 1 I converted the time frames from text like ANNUAL to # months => 12

If the cell has text (i.e. ISNUMBER is false) then it ignores the cell

Then using EOMONTH I compare the date in the cell + the corresponding # months to today or # months -1 for the Orange warning.

[#4]

@mtarler Thankyou, is there a way it will work using GB date format DD/MM/YYYY?

[#5]

The date format doesn't matter. The conditional formatting is calculated based purely on the date VALUE. The date FORMAT can be set using the cell formatting. With respect to the default date format and what excel recognizes a date as when typed in, that is based on windows regional settings.