Formula Errors

How to Create Icon Set Conditional Formatting Based on Dates in Excel

John Wilson Sep 28, 2026 873 views

Question details

The user wants to display red, amber, and green status icons based on a comparison between forecast dates, actual dates, and the current date (TODAY).

How to Create Icon Set Conditional Formatting Based on Dates in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking project deadlines or task statuses visually, requiring dynamic updates when actual completion dates are entered or when the current date surpasses the forecast date.
Observed behavior: Needs to apply conditional formatting icon sets using complex multiple-condition date rules without displaying the underlying formula numbers.

Before you start

Ensure your forecast and actual columns contain valid date formats, not text strings, so that the TODAY() function can correctly calculate time differences.

Solution 1Recommended

Use a Helper Column with Nested IF Functions

By using a helper column to output a simple number (1, 2, or 3) based on your date conditions, you can easily apply an icon set rule that hides the numbers.

Applying icon sets directly to dates using multiple overlapping logic rules (like checking if an actual date exists while comparing a forecast date to today) is highly complex. A better approach is to use a helper formula that assigns a discrete value to each status.

For example, let's assign 1 for Green (On Track), 2 for Amber (Pending/Warning), and 3 for Red (Overdue).

Create the helper formula

In a new 'Status' column, enter a nested IF formula that evaluates your dates. For example: =IF(ActualDate="", IF(ForecastDate<TODAY(), 3, 2), IF(ActualDate<=ForecastDate, 1, 3)). Drag this formula down for all rows.

Apply the Icon Set

Select the cells containing your new helper formula. Go to the Home tab, click 'Conditional Formatting', hover over 'Icon Sets', and choose the 3 Traffic Lights (Unrimmed).

Edit the Formatting Rule

With the cells still selected, go to 'Conditional Formatting' > 'Manage Rules'. Select the Icon Set rule and click 'Edit Rule'.

Hide numbers and assign values

Check the box labeled 'Show Icon Only'. Change the 'Type' dropdowns to 'Number'. Set the Green icon to when value is >= 1, Amber to >= 2 (if structured inversely) or map the red/amber/green icons strictly to your 1, 2, 3 outputs by clicking the icon dropdowns. Click OK to apply.

Use a Helper Column with Nested IF Functions

Dynamic Updates: Because the formula utilizes the TODAY() function, your red and amber statuses will automatically update every time you open the workbook.

Easily Track Project Deadlines with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting, nested IF formulas, and date functions. You can seamlessly create visual status trackers and traffic light icon sets with an intuitive rule manager.

1. Open your tracking sheet: Launch WPS Spreadsheet and open your workbook containing the forecast and actual date columns.
2. Add a status formula: Create a new column and enter your nested IF formula to calculate a 1, 2, or 3 value based on the date logic.
3. Apply Icon Sets: Highlight the results, click 'Conditional Formatting' on the Home tab, and select 'Icon Sets' to insert your preferred indicators.
4. Configure 'Show Icon Only': Click 'Conditional Formatting' > 'Manage Rules' > 'Edit Rule', then check 'Show Icon Only' to display clean, professional visual indicators without the underlying numbers.

100% compatible with Microsoft Excel conditional formatting and date formulasIntuitive and easy-to-navigate conditional formatting rule managerFree to use for everyday data analysis and project trackingLightweight software that processes heavy formulas rapidly

Frequently Asked Questions

Can I base an icon set directly on dates without using a helper column?

Yes, but it is limited to simpler scenarios. When dealing with complex logic (e.g., checking if a cell is blank, comparing forecast vs actual, and comparing to TODAY simultaneously), Excel's built-in icon set rules cannot handle all conditions natively. A helper column drastically simplifies the logic.

Why are the numbers still showing next to my traffic light icons?

By default, Excel displays the cell's value alongside the icon. To fix this, highlight the formatted cells, go to Conditional Formatting > Manage Rules > Edit Rule, and check the 'Show Icon Only' box. This will hide the formula results and leave only the traffic lights.

How do I make the formula treat an empty actual date as 'not finished' instead of an error?

You can handle empty date cells by starting your nested IF formula with a check for blanks. For example, IF(ISBLANK(B2), ..., ...) or IF(B2="", ..., ...). This directs Excel to evaluate the current date against the forecast date instead of attempting to calculate an empty cell.

Will the icons update automatically tomorrow?

Yes. If your helper formula uses the TODAY() function, the spreadsheet will recalculate the current date whenever the file is opened or whenever any calculation is triggered, automatically shifting pending items to overdue (Red) if the deadline has passed.

Excel & Data Problems