Formula Errors

How to Use Excel Conditional Formatting for Overdue and Blank Dates

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to highlight overdue dates in a spreadsheet column while keeping cells blank if the source date column has no entry, avoiding the 1900 date error.

Product: Excel
Device & OS: not provided
Scenario: Setting up a case-management worksheet that calculates due dates and automatically flags overdue tasks using conditional formatting.
Observed behavior: When the source date cell is blank, the formula cell calculates a zero value, causing the date format to incorrectly display as January 0, 1900.

Before you start

Ensure your date columns are properly formatted as 'Date' rather than 'General' or 'Text', and verify which column contains your base dates.

Solution 1Recommended

Use an IF Function Combined with Conditional Formatting

By wrapping your date calculation in an IF function, you can force blank source cells to output empty strings. You can then apply a dynamic TODAY() rule to highlight the genuinely overdue dates.

In Excel, an empty cell evaluates to zero in a formula. Since Excel's calendar system starts on January 1, 1900, a zero value formatted as a date will always display as 'January 0, 1900'. Combining an IF formula with conditional formatting resolves both the display issue and the highlighting requirement.

Apply the IF formula to handle blanks

In your target due date cell (for example, J3), enter the formula: =IF(I3="","",I3+H3). This instructs Excel to output a blank cell if column I is empty, or calculate the due date if it is not.

Set up conditional formatting for overdue dates

Select the entire range of your due dates (e.g., Column J). Navigate to Home > Conditional Formatting > Highlight Cells Rules > Less Than. Type =TODAY() in the criteria box and select a format, such as Light Red Fill with Dark Red Text.

Convert the data range to an Excel Table

Select your entire dataset and press Ctrl+T. Converting your data into an official Table ensures that whenever you add new rows for future cases, your IF formula and conditional formatting rules will automatically expand without manual copying.

Streamlined Tracking: Using a Table layout is highly recommended for case management, as it preserves data validation and styling rules effortlessly across all new entries.

Manage Dates Effectively

Easily Highlight Overdue Dates in WPS Spreadsheet

WPS Spreadsheet provides robust formula support and intuitive conditional formatting completely compatible with Excel, helping you manage task deadlines accurately.

1. Open your worksheet: Launch WPS Spreadsheet and open your case-management file.
2. Input the IF formula: Type =IF(I3="","",I3+H3) in the target column to prevent 1900-date errors.
3. Set Conditional Formatting: Navigate to the Home tab, click Conditional Formatting > Highlight Cells Rules, and set the condition to less than =TODAY().

Fully compatible with Microsoft Excel formulas like IF and TODAY()Intuitive Conditional Formatting menu for quick date highlightingLightweight, fast, and free alternative to manage daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show January 0, 1900 for blank date cells?

Excel stores dates as serial numbers starting from January 1, 1900. When a formula references an empty cell, Excel interprets the blank as a zero. When formatted as a date, this zero serial number translates to the non-existent date of January 0, 1900.

Can I also highlight dates that are approaching their deadline?

Yes. You can add a second Conditional Formatting rule using the 'Between' option. Set the range to =TODAY() and =TODAY()+7, and choose a different color like yellow to warn you about tasks due in the next 7 days.

Will the IF formula affect sorting and filtering in my table?

No. The IF formula returning a blank text string ("") will still allow you to sort and filter your column effectively. By default, cells with empty strings will appear at the bottom when sorting from oldest to newest.

Excel & Data Problems