Formula Errors

Fix Excel Conditional Formatting Date Formula Not Working

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is attempting to set up a conditional formatting rule to highlight a date cell green after three days, but the applied formula using the TODAY() function fails to trigger the color change.

Product: Excel
Device & OS: not provided
Scenario: Applying conditional formatting based on a dynamic date calculation, specifically to highlight past-due items three days after a recorded date.
Observed behavior: The formula =AND(I2<>"",J2="",TODAY()>I2+2) does not change the cell color as expected even when the conditions appear to be met.

Before you start

Ensure that the cells containing your dates are formatted as actual Excel date values rather than text strings, and verify that any target cells expected to be empty do not contain hidden spaces.

Solution 1Recommended

Apply the Correct Date-Difference Formula

Replace the existing conditional formatting logic with a more reliable date-difference subtraction formula to accurately evaluate the elapsed days.

Instead of checking if today is greater than the cell date plus two days, subtracting the cell date directly from TODAY() provides a cleaner comparison that Excel processes more reliably in conditional formatting engines.

Select the target range

Highlight the range of cells where you want the conditional formatting to apply.

Open Conditional Formatting Manager

Go to the Home tab, click on Conditional Formatting, and select Manage Rules.

Edit the existing rule

Select your current rule and click Edit Rule, or create a New Rule and choose 'Use a formula to determine which cells to format'.

Input the corrected formula

Enter the optimized formula: =AND(I2<>"", J2="", TODAY()-I2>=3).

Apply and save

Ensure your format settings (like a green fill color) are applied, click OK, and then Apply.

Alternative Formula: You can also use the built-in DAYS function: =AND(I2<>"", J2="", DAYS(TODAY(), I2)>=3).

Solution 2

Synchronize Formula References with the Applied Range

Conditional formatting evaluates the formula for each cell dynamically. If your row references are misaligned with your selected range, the formatting will apply to the wrong rows or not at all.

Solution 3

Verify Cell Data Types

Date formulas like TODAY() cannot calculate correctly if the target cells are stored as text rather than valid serial date numbers.

Easily Manage Conditional Formatting with WPS Spreadsheet

You can easily troubleshoot and set up advanced date-based conditional formatting using WPS Spreadsheet, which offers a familiar interface, robust formula evaluation, and seamless format management for your daily workflows.

1. Open your file: Launch WPS Spreadsheet and open your workbook.
2. Select the range: Highlight the data range you want to format dynamically.
3. Create the rule: Navigate to the Home tab, click on Conditional Formatting, and select New Rule.
4. Apply the formula: Choose 'Use a formula to determine which cells to format', input your date formula, set the desired highlight color, and click OK.

100% format compatibility with Microsoft Excel (.xlsx) filesFull support for advanced date functions like TODAY() and DAYS()Intuitive Conditional Formatting Manager to easily visualize rules and rangesFree, lightweight, and fast office suite for all productivity needs

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting highlight the wrong rows?

This usually happens when the row number referenced in your formula does not align with the starting row of your selected range. Always ensure that if your selected 'Applies to' range starts at row 2, your formula references row 2 (e.g., I2 instead of I1).

Why isn't TODAY() updating automatically in my spreadsheet?

The TODAY() function relies on workbook recalculation. If your calculation options are set to manual, formulas won't refresh when the date changes. Go to the Formulas tab, select Calculation Options, and change it to Automatic, or press F9 to force a recalculation.

Can I use the DAYS function instead of simple subtraction for dates?

Yes, you can use the DAYS function as a reliable alternative. A formula like =AND(I2<>"", J2="", DAYS(TODAY(), I2)>=3) calculates the exact difference in days between the current date and the cell date.

Excel & Data Problems