Formula Errors

How to Fix Excel Icon Set Conditional Formatting for Date Statuses

Nimra Malik Sep 28, 2026 871 views

Question details

The user needs to correctly set up RAG (Red, Amber, Green) status icons using conditional formatting based on date comparisons, but is encountering mismatched icons due to formula errors.

How to Fix Excel Icon Set Conditional Formatting for Date Statuses

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Setting up visual RAG status indicators by comparing forecast and actual dates using the TODAY() function.
Observed behavior: Icon sets display mismatched indicators because of incorrect AND logic and overlapping threshold values in the conditional formatting rules.

Before you start

Verify that your data columns for forecast and actual dates contain valid date formats, and decide on the exact numerical values (e.g., 1, 2, 3, 4) you will map to each icon in your rule.

Solution 1Recommended

Correct the Nested IF and AND Formula Logic

Use a properly structured nested formula to return distinct integer values for each date condition before applying icon sets.

Conditional formatting icon sets work best when referencing a distinct set of numbers. By calculating these numbers in the cell first, you avoid relying on Excel to calculate percentages or complex formulas directly inside the formatting rule manager.

Select the target cell

Click on the cell in your status column where you want the RAG status indicator to appear.

Enter the nested logic formula

Type your structured formula that returns distinct values based on dates. For example: =IF(I3=K3,1,IF(AND(I3>TODAY()+2,K3=""),2,IF(AND(I3>TODAY()+4,K3=""),3,IF(I3<=TODAY(),4,5))))

Review the date gaps

Check your logical conditions to ensure there are no missing date gaps (such as dates exactly 1 day after TODAY(), which might break the sequence).

Apply formula to the column

Drag the fill handle at the bottom right corner of the cell to copy the formula down through the rest of your status column.

Correct the Nested IF and AND Formula Logic

Avoid Logic Gaps: Always check edge cases, such as dates falling exactly on 'tomorrow', to ensure every possible date difference triggers a specific integer.

Solution 2

Adjust Conditional Formatting Icon Set Thresholds

Modify the conditional formatting rules to map directly to your calculated integer values without overlapping.

Professional Data Management

Use WPS Spreadsheet to Easily Apply Date-Based Icon Sets

WPS Spreadsheet provides intuitive tools for conditional formatting and complex nested IF formulas. Effortlessly set up RAG statuses for project trackers and enjoy a smooth, user-friendly interface.

1. Open your document: Launch WPS Spreadsheet and open the file containing your project dates.
2. Apply date formulas: Enter your nested IF and AND date comparison formulas to generate distinct integer values.
3. Access Conditional Formatting: Select the data range, navigate to the Home tab on the ribbon, and choose Conditional Formatting.
4. Configure Icon Sets: Select Icon Sets, click 'More Rules', change the value types to 'Number', and assign your specific thresholds.
5. Save your work: Check 'Show Icon Only' and save your highly visual project tracker in the universally compatible .xlsx format.

Full compatibility with Microsoft Excel formulas and formatting.Intuitive Conditional Formatting rule manager.Seamlessly handle date functions like TODAY().Free, lightweight, and highly responsive interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say my conditional formatting values overlap?

This error occurs when the mathematical thresholds set in the 'Icon Sets' rule manager conflict with each other or leave no valid boundary. Ensure you change the value type from 'Percent' to 'Number' and that each threshold has a strictly distinct, non-overlapping boundary.

How do I hide the numbers and only show the icons?

In the 'Edit Formatting Rule' dialog box for Icon Sets, check the box labeled 'Show Icon Only'. This will hide the formula's calculated output numbers and display only the visual RAG indicators in the cells.

Can I reverse the order of the icons in conditional formatting?

Yes. When editing the Icon Set rule, click the 'Reverse Icon Order' button to easily swap the sequence. This is especially useful when smaller calculated numbers should represent a 'Green' or better status.

Excel & Data Problems