Formatting Issues

How to Change Excel Cell Color Based on Time Since a Date

Ayan Masood Oct 10, 2026 870 views

Question details

The user wants to automatically apply green, amber, or red colors to cells depending on how much time has passed since a given date.

How to Change Excel Cell Color Based on Time Since a Date

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking deadlines, expirations, or past-due items using a dynamic, color-coded traffic light system.
Observed behavior: Cells dynamically change background color as the current date progresses relative to the static date entered in the cell.

Before you start

Ensure your date columns contain valid date values rather than text strings, so that formulas utilizing TODAY() and EDATE() can calculate properly.

Solution 1Recommended

Use Formula-Based Conditional Formatting Rules

Create custom rules using the TODAY and EDATE functions to set specific color thresholds based on exact months passed.

This method allows for precise control over your traffic-light color system by combining logical formulas with conditional formatting.

Select Date Cells

Highlight the range of cells containing the dates you want to format (e.g., A1 through A100).

Open Conditional Formatting

Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.

Create the Red Rule

Choose 'Use a formula to determine which cells to format'. Enter the formula =A1<=EDATE(TODAY(),-11) (assuming A1 is the first cell). Click 'Format', choose a red fill color, and click OK.

Create the Amber Rule

Repeat the New Rule process. Enter the formula =AND(A1>EDATE(TODAY(),-11),A1<=EDATE(TODAY(),-6)). Click 'Format', choose an amber or yellow fill color, and click OK.

Create the Green Rule

Create one final rule using the formula =A1>EDATE(TODAY(),-6). Click 'Format', choose a green fill color, and confirm.

Use Formula-Based Conditional Formatting Rules

Cell Reference Adjustment: Be sure to replace 'A1' in the formulas with the actual top-left cell of your selected range without using absolute dollar signs (e.g., avoid $A$1) so the rule applies correctly to every cell.

Solution 2

Use Cell Value-Based Formatting Rules

Alternatively, apply conditional formatting directly to cell values relative to a dynamically calculated threshold date.

Dynamically Format Dates with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting rules and native Excel date formulas like TODAY and EDATE. You can seamlessly establish a color-coded tracking system in a clean, intuitive interface.

1. Open Your Workbook: Launch WPS Spreadsheet and select the column or range of dates you wish to color code.
2. Access Conditional Formatting: On the Home tab ribbon, locate and click the 'Conditional Formatting' icon, then select 'New Rule'.
3. Input the Date Formula: Choose 'Use a formula to determine which cells to format', and input your EDATE and TODAY logic (e.g., =A1<=EDATE(TODAY(),-11)).
4. Apply Colors: Click the Format button to pick your desired red, amber, or green fill, then click OK to apply it instantly to your data.

Fully compatible with Microsoft Excel (.xlsx) formulas and formattingRobust conditional formatting rule manager for easy adjustmentsLightweight, lightning-fast, and completely free to useFamiliar tabbed interface makes migrating from Excel effortless

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my cells changing color when the date changes?

Formulas relying on TODAY() only update when the spreadsheet recalculates. If your colors aren't updating, press F9 to force a manual recalculation, or simply save and reopen the file.

How do I highlight dates that will expire in the next 30 days?

Use a formula-based rule with the condition =AND(A1>=TODAY(), A1<=TODAY()+30) to highlight dates falling strictly within the upcoming 30-day window.

Can I apply the conditional formatting color to the entire row?

Yes. Select your entire data table before creating the rule. When writing the formula, lock the column reference for the date using a dollar sign (e.g., =$A1<=EDATE(TODAY(),-11)). This ensures the whole row looks at column A to determine its color.

What if my conditional formatting formulas aren't working on downloaded data?

Your exported dates may be formatted as text. Excel cannot perform EDATE or TODAY comparisons on text strings. Highlight the column, go to the Data tab, and click 'Text to Columns' > Finish to instantly convert them into proper date formats.

Document & File Issues