Forum Discussion
Conditional formatting for expiry dates
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
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.
- mtarler Thankyou, is there a way it will work using GB date format DD/MM/YYYY?
- 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.
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.
I know I don't know anything (Socrates)
