Formatting Issues

How to Keep an Excel Date Cell Green for a Year and Turn Red

Maira Mehtab Sep 21, 2026 871 views

Question details

The user wants to format a specific cell to maintain a green background color for exactly one year from a given date, and then automatically change to a red background once that year has elapsed.

Product: Excel
Device & OS: not provided
Scenario: Tracking expiration dates, warranties, annual subscriptions, or employee reviews where a visual color cue is required to instantly identify valid versus expired items.
Observed behavior: The user needs the cell formatting to dynamically apply green when the date is within the one-year timeframe and switch to red when it falls outside or exceeds that period.

Before you start

Ensure your cells are properly formatted as Dates rather than Text, as conditional formatting formulas require valid date values to evaluate correctly.

Solution 1Recommended

Use Dynamic Conditional Formatting Based on Today's Date

Set a default red fill color and apply a conditional formatting rule using the EDATE and TODAY functions to keep dates within the past year green. This is ideal for rolling one-year tracking.

By setting the default background to red, you only need to create one conditional formatting rule to turn the cell green when the condition is met. This reduces complexity and improves spreadsheet performance.

Set Default Cell Color

Select the target cell (e.g., B5). Go to the 'Home' tab on the ribbon, click the 'Fill Color' bucket icon, and choose a Red color.

Create a New Formatting Rule

With the cell still selected, click on 'Conditional Formatting' in the 'Home' tab, then select 'New Rule' from the dropdown menu.

Enter the Date Formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter `=B5>EDATE(TODAY(),-12)`. This checks if the date in B5 is greater than the date exactly 12 months ago.

Apply Green Formatting

Click the 'Format' button, navigate to the 'Fill' tab, select a Green color, and click 'OK' twice to apply the rule.

Formula Tip: The EDATE function dynamically calculates months in the past or future. Using -12 easily calculates one year backward from today.

Solution 2

Apply Conditional Formatting for a Fixed Date Range

If you need the color change tied to a specific hardcoded date instead of the current day, use the DATE function to create a static window.

WPS Spreadsheet Solution

Easily Manage Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for applying complex conditional formatting rules, allowing you to seamlessly track dates and expiration periods with visual cues.

1. Open Your Document: Launch WPS Spreadsheet and open the document containing your date tracking list.
2. Select Target Cells: Highlight the date cells you wish to track. Apply a standard red background color.
3. Apply Rule: Navigate to Home > Conditional Formatting > New Rule. Enter your date tracking formula and configure the green fill color.

Fully compatible with Microsoft Excel conditional formatting rules and formulasFree and lightweight alternative to Microsoft OfficeIntuitive interface for easily managing date formulas like EDATE and TODAYCross-platform support for Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting formula working on the dates?

Your date values might be stored as text. Select the problematic cells, go to the Home tab, and ensure the number format is set to 'Short Date' or 'Long Date'. You can also try retyping the date to force Excel to recognize it.

Can I apply this conditional formatting to an entire column at once?

Yes. Select the entire column (for example, Column B), apply the default red fill, and use the formula =B1>EDATE(TODAY(),-12) in your conditional formatting rule. The formula will automatically adapt the cell reference for each row in the column.

What does the EDATE function do in the conditional formatting formula?

The EDATE function returns the serial number of a date that is a specific number of months before or after a starting date. In the formula EDATE(TODAY(), -12), it calculates the exact date 12 months (one year) prior to today's date.

Document & File Issues