Formatting Issues

How to Create an Excel Traffic-Light Countdown with Conditional Formatting

John Wilson Oct 13, 2026 870 views

Question details

The user needs to set up a color-coded traffic-light status system (green, yellow, and red) to track contract renewal dates based on the number of days remaining.

How to Create an Excel Traffic-Light Countdown with Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Building a contract tracker or deadline management spreadsheet that visually flags impending renewal dates.
Observed behavior: The goal is to automatically format cells in green for more than 60 days remaining, yellow for 30 to 60 days remaining, and red for less than 30 days remaining.

Before you start

Ensure that your renewal dates are formatted properly as Dates in your spreadsheet so that the logical formulas can correctly calculate the days remaining based on the current date.

Solution 1Recommended

Apply Formula-Based Conditional Formatting Rules

Use the TODAY() function inside conditional formatting rules to dynamically change cell fill colors as deadlines approach.

By utilizing custom formulas in the conditional formatting menu, you can evaluate the dates in your tracking column against the current date. This method automatically updates every time you open the workbook.

Select your date range

Highlight the cells containing your renewal dates. For example, if your dates are in column D and start at row 2, select D2 down to the end of your dataset. Make sure D2 is your active cell.

Create the Red rule (Less than 30 days)

Navigate to Home > Conditional Formatting > New Rule, and select 'Use a formula to determine which cells to format'. Enter the formula: =AND($D2>=TODAY(),$D2<TODAY()+30). Click 'Format', choose a red fill color, and click OK.

Create the Yellow rule (30 to 60 days)

Add another new rule using the formula option. Enter the formula: =AND($D2>=TODAY()+30,$D2<TODAY()+60). Click 'Format', choose a yellow fill color, and click OK.

Create the Green rule (More than 60 days)

Add a final new rule. Enter the formula: =$D2>=TODAY()+60. Click 'Format', select a green fill color, and save the rule.

Apply Formula-Based Conditional Formatting Rules

Locking the Column Reference: Using a mixed reference like $D2 ensures the formula locks onto column D but dynamically adapts to each row (D3, D4, etc.) as the rule is applied down your spreadsheet.

Manage Deadlines Easily with WPS Office

Create Automated Contract Trackers in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting and dynamic date functions like TODAY(). You can easily build visual traffic-light trackers to monitor deadlines and contract renewals without needing complex macros.

1. Open your tracker: Launch WPS Spreadsheet and open your document containing the deadline dates.
2. Access Conditional Formatting: Highlight your date column, navigate to the Home tab, and select 'Conditional Formatting' > 'New Rule'.
3. Apply TODAY() formulas: Select the formula option, input your date criteria for red, yellow, and green, then assign your fill colors.

Full compatibility with Microsoft Excel formulas and conditional formatting rulesRich library of pre-built templates for project and contract managementLightweight architecture that runs smoothly even with large trackers

microsoft office alternative - wps office

Frequently Asked Questions

Can I apply the traffic light colors to the entire row instead of just the date cell?

Yes. Before creating your conditional formatting rules, select your entire data table (e.g., A2:F100) instead of just the date column. As long as your formula uses an absolute column reference (like $D2), the entire row will change color based on the date in column D.

Why are my conditional formatting colors applying to the wrong rows?

This usually happens if the active cell when creating the rule doesn't match the row number in your formula. If your 'Applies to' range starts at D2, ensure your formula is written for row 2 (e.g., $D2).

How do I format dates that have already passed (overdue)?

The current red rule (=AND($D2>=TODAY(),$D2<TODAY()+30)) only highlights dates within the next 30 days. To include dates in the past, change the red formula to simply =$D2<TODAY()+30, or create a separate rule with a dark red color using the formula =$D2<TODAY().

Document & File Issues