Excel Conditional Formatting for Expiry Dates in Green, Amber, and Red
Question details
Create color-coded conditional-formatting rules for expiry dates while excluding cells marked Not required.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet tracks expiry dates and needs a one-month warning period.
- Observed behavior
- Dates more than one month away should be green, dates within one month should be amber, and expired or blank cells should be red. Not required should remain unformatted.
Ensure expiry dates are stored as actual Excel dates, not text, and identify the first cell and range to format. The examples below assume date-only values in A2:A100 and use a calendar month as the warning period.
Create three formula-based expiry date rules in Excel
Use these rules to update the colors automatically as today's date changes.
A date equal to today is amber; it turns red the following day. Not required and other nonblank text are excluded because the date rules check for numeric values.
Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter =OR($A2="",AND(ISNUMBER($A2),$A2<TODAY())), select Format, choose a red fill, and click OK.
Choose Home > Conditional Formatting > New Rule and select the formula option again. Enter =AND(ISNUMBER($A2),$A2>=TODAY(),$A2<=EDATE(TODAY(),1)), choose an amber fill, and click OK.
Create another formula rule with =AND(ISNUMBER($A2),$A2>EDATE(TODAY(),1)), choose a green fill, and click OK.
Open Home > Conditional Formatting > Manage Rules and confirm that each rule applies to =$A$2:$A$100. If your dates are elsewhere, adjust the column and first-row references to match your selected range.

Track expiry dates with conditional formatting in WPS Spreadsheets
Use formula-based conditional formatting in WPS Spreadsheets to highlight expired, approaching, and valid dates while leaving Not required unformatted.
- 1. Select the dates: Open the worksheet in WPS Spreadsheets and select A2:A100, or select your own date range and adjust the formulas accordingly.
- 2. Create the red rule: Choose Home > Conditional Formatting > New Rule, select the formula-based rule option, and enter =OR($A2="",AND(ISNUMBER($A2),$A2<TODAY())). Set the fill to red and save the rule.
- 3. Create the amber rule: Add another formula-based rule with =AND(ISNUMBER($A2),$A2>=TODAY(),$A2<=EDATE(TODAY(),1)) and set its fill to amber.
- 4. Create the green rule: Add a third formula-based rule with =AND(ISNUMBER($A2),$A2>EDATE(TODAY(),1)) and set its fill to green.

Frequently Asked Questions
What color is an expiry date that falls today?
It is amber under these rules. The red rule uses a date earlier than TODAY(), so the date becomes red the next day.
Why is a date not changing color?
The cell may contain a date stored as text. Convert it to a real Excel date, then check the range under Home > Conditional Formatting > Manage Rules.
How can I change the warning period to two months?
In both the amber and green formulas, replace EDATE(TODAY(),1) with EDATE(TODAY(),2). This keeps the two rules from overlapping.
Will cells marked Not required turn red?
No. The red rule targets blank cells and numeric dates earlier than today; the amber and green rules also require numeric dates.




