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...
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.
