Formatting Issues

How to Fix Excel Conditional Formatting for Date and Time Values

Maira Mehtab Sep 27, 2026 869 views

Question details

Users need to resolve an issue where date-based conditional formatting rules fail to apply to date and time values in an Excel spreadsheet.

Product: Excel
Device & OS: not provided
Scenario: Applying date-specific conditional formatting rules (such as Yesterday, Today, or Tomorrow) to a column containing date and time entries.
Observed behavior: Conditional formatting rules fail to trigger because the date and time values are improperly stored as text or in an invalid format rather than recognized date-time serial numbers.

Before you start

Check the alignment of your date and time cells; by default, Excel left-aligns text and right-aligns valid numbers and dates. If your dates are left-aligned, they are likely stored as text.

Solution 1Recommended

Convert Text to Valid Date-Time Format

Ensure Excel properly recognizes your entries as true dates and times so that conditional formatting rules can function correctly.

Excel's conditional formatting rules rely on underlying numerical serial numbers to recognize dates. If data is imported or typed as text, you must convert these text strings into valid date-time formats before applying any rules.

Select the target cells

Highlight all the cells containing the date and time values that are failing to format.

Convert text to columns

Navigate to the Data tab on the ribbon and click on 'Text to Columns'. Proceed through the wizard, ensuring you select 'Date' in the third step to parse the text into valid date formats.

Apply correct number formatting

Alternatively, right-click the selected cells, choose 'Format Cells', go to the 'Number' tab, select 'Date' or 'Custom', and input a valid format like 'dd/mm/yyyy hh:mm'.

Apply conditional formatting

With the corrected cells highlighted, go to Home > Conditional Formatting > Highlight Cells Rules, and select 'A Date Occurring' to apply rules like 'Yesterday' or 'Today'.

Verification Tip: You can temporarily change the cell format to 'General'. If the date turns into a 5-digit number (e.g., 45231), it is recognized as a valid date by Excel.

WPS Spreadsheet Solution

Fix Date Formatting Easily with WPS Spreadsheet

WPS Office provides a highly capable Spreadsheet application that effortlessly handles complex date-time conversions and conditional formatting, while being fully compatible with Microsoft Excel files.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing the unrecognized date values.
2. Format the cells: Select the problematic cells, right-click, choose 'Format Cells', and select the 'Date' category to standardize the entries.
3. Access Conditional Formatting: Navigate to the 'Home' tab on the top ribbon, click 'Conditional Formatting', and hover over 'Highlight Cells Rules'.
4. Apply the date rule: Click 'A Date Occurring...', choose your desired timeframe from the dropdown menu, and click 'OK' to apply the formatting.

Intuitive tools to quickly convert text strings into valid date and time values.Full format compatibility with Microsoft Excel (.xlsx and .xls) files.Comprehensive 'Highlight Cells Rules' for easy date-based conditional formatting.Free, lightweight, and user-friendly interface that requires no learning curve.

Frequently Asked Questions

Why does my conditional formatting ignore dates imported from a CSV file?

When importing from a CSV file, Excel frequently interprets dates as text strings, especially if the regional date settings on your computer do not match the CSV format. Converting these text values to valid date formats using the 'Text to Columns' tool will allow conditional formatting to recognize them.

How can I quickly identify if a date is stored as text?

The easiest visual check is cell alignment: text is left-aligned, whereas valid dates are right-aligned by default. You can also use the formula =ISTEXT(A1) (replace A1 with your cell reference); if it returns TRUE, the date is stored as text.

Can I format cells based on both date and time values simultaneously?

Yes. If your cell contains a combined date and time value (e.g., 31/10/2024 14:00), the underlying serial number includes fractions for the time. Standard 'A Date Occurring' rules will evaluate the integer (date) portion, but you can also use custom formula rules like =AND(A1>=TODAY(), A1<TODAY()+1) for more precise control.

Excel & Data Problems