Function Problems

How to Use Excel Icon Sets to Compare Target and Actual Dates

Guest Writer Sep 28, 2026 869 views

Question details

The user wants to display specific icons based on whether an actual date is later, equal to, or earlier than a target date by row.

How to Use Excel Icon Sets to Compare Target and Actual Dates

Product: Excel
Device & OS: not provided
Scenario: Managing projects or tracking task deadlines where a visual comparison between planned target dates and actual completion dates is required.
Observed behavior: The user needs to set up rules so that actual dates later than target dates show a red icon, matching dates show yellow, and earlier dates show green.

Before you start

Ensure that your target and actual dates are formatted as recognizable Date values in Excel, rather than plain text, so that the comparison formulas can calculate correctly.

Solution 1Recommended

Use a Helper Column with the SIGN Function and Built-in Icon Sets

Create a helper column to calculate the mathematical difference between the dates and apply a default icon set directly to the results for a quick visual.

This method is often the simplest because it utilizes Excel's built-in 3-traffic-light icon sets perfectly. The SIGN function evaluates the difference between the two dates and returns 1 (late), 0 (on time), or -1 (early), which maps cleanly to the default icon rules.

Create a Helper Column

Add a new column next to your actual dates, for example, column P if Actual Dates are in O and Target Dates are in N.

Enter the SIGN Formula

In the first data cell of the helper column (e.g., P4), type the formula =SIGN(O4-N4) and press Enter.

Fill Down the Formula

Click the bottom-right corner of the cell containing the formula and drag the fill handle down to apply it to all rows in your dataset.

Apply the Icon Set

Select all the helper column cells. Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', hover over 'Icon Sets', and select the three-circle traffic lights (Red, Yellow, Green).

Hide the Numbers (Optional)

To show only the icons, go to 'Conditional Formatting' > 'Manage Rules'. Select the icon set rule, click 'Edit Rule', check the 'Show Icon Only' box, and click 'OK'.

Use a Helper Column with the SIGN Function and Built-in Icon Sets

Formula Logic: The SIGN function outputs 1 when O4 > N4 (Actual is later), which triggers the red icon; 0 when they are equal, triggering yellow; and -1 when O4 < N4 (Actual is earlier), triggering green.

Solution 2

Apply Custom Conditional Formatting Rules with Formulas

Set up three distinct conditional formatting rules using relative formulas to apply specific formats or icons without needing an extra helper column.

Project Tracking Solution

Easily Compare Dates and Track Projects with WPS Spreadsheet

WPS Office offers a powerful, free alternative to Microsoft Excel with full support for advanced conditional formatting, relative formula calculations, and built-in icon sets to help you manage project timelines effortlessly.

1. Open Your Tracker: Launch WPS Spreadsheet and open your project file containing the target and actual dates.
2. Select the Data: Highlight the helper column or the actual date cells you wish to format.
3. Access Conditional Formatting: Click on the 'Home' tab, locate the 'Conditional Formatting' button, and choose 'Icon Sets' or 'New Rule'.
4. Apply Formulas: Enter the relative formulas or select the desired traffic light icons to visualize the date status.
5. Save and Monitor: Click 'OK' to instantly view late, on-time, and early tasks visually, then save your highly compatible document.

Seamlessly apply custom conditional formatting for target vs. actual date comparisons.Fully compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Built-in library of professional icon sets for visual task tracking.Free, lightweight, and user-friendly interface for Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my date comparison formulas working correctly?

This usually happens when one or both of the columns contain dates stored as text rather than valid serial numbers. Select your dates, right-click, choose 'Format Cells', and ensure they are categorized as 'Date'. You can also use the 'Text to Columns' feature to convert text dates into serial numbers.

Can I hide the helper column numbers but keep the icons visible?

Yes. When managing your conditional formatting rule for the icon sets, click 'Edit Rule'. Check the box labeled 'Show Icon Only' and click 'OK'. The numbers will disappear, leaving only the colored icons in the cell.

How do I remove the conditional formatting rules if I make a mistake?

Select the cells containing the incorrect formatting, go to the 'Home' tab, click 'Conditional Formatting', hover over 'Clear Rules', and choose 'Clear Rules from Selected Cells'.

Can I use different icons instead of traffic lights?

Absolutely. When setting up or editing your Icon Set rule, you can use the dropdown menus next to each rule threshold to select different symbols, such as checkmarks, flags, or arrows, according to your preference.

Excel & Data Problems