Fix Excel TODAY Function Returning FALSE for Matching Dates
Question details
The user needs to understand why comparing seemingly identical dates to the TODAY() function returns FALSE, and how to resolve this for accurate conditional formatting or logical tests.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up conditional formatting or executing logical comparison formulas using the TODAY() function.
- Observed behavior
- Excel treats displayed dates as different from TODAY() even when they look visually identical, resulting in unexpected FALSE outputs in formulas and conditional formatting.
Ensure your spreadsheet recalculation is set to Automatic so the TODAY() function updates correctly, and verify your system clock is displaying the correct date.
Strip Hidden Time Values Using the INT Function
Dates in Excel might contain hidden time values (fractions of a day). Since the TODAY() function returns a whole number representing midnight, using the INT function ensures an exact match.
Excel stores dates as serial numbers and times as decimal fractions. If a date cell was generated with a timestamp (like NOW()) or imported from another system, it may carry a hidden decimal value. The TODAY() function generates an integer without decimals. Comparing the two will yield a FALSE result.
Click on the cell containing your logical test or conditional formatting rule that is returning FALSE.
Wrap the target cell reference with the INT() function to remove any decimal time values. For example, change =A2=TODAY() to =INT(A2)=TODAY().
Press Enter to apply the formula. The INT function will strip the hidden time component, allowing the exact date match to return TRUE.
Convert Text-Formatted Dates to Serial Date Values
If dates are stored as text instead of numeric serial values, Excel cannot logically compare them to the numerical output of the TODAY() function.
Use the WORKDAY Function for Formula-Driven Dates
If the date is derived from another complex formula, standardizing the output using the WORKDAY function ensures it generates a clean, comparable date serial number.
Fix Date Formula Errors Easily in WPS Spreadsheet
WPS Spreadsheet offers robust formula tracking and formatting tools to easily identify hidden time values and fix date comparison errors like the TODAY() function returning FALSE.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the date errors.
- 2. Check Cell Format: Right-click the problematic date cells and select 'Format Cells' to verify they are stored as Dates, not Text.
- 3. Apply Formula Fixes: Use the INT() or DATEVALUE() functions directly in the WPS formula bar to normalize the data.
- 4. Set Conditional Formatting: Go to the Home tab, click Conditional Formatting, and safely apply your corrected TODAY() rules.
Frequently Asked Questions
Why does my date look exactly like TODAY() but returns FALSE?
Excel dates are stored as numeric serial numbers. While a cell might display '10-Oct-2023', the underlying value might include a hidden time fraction (e.g., 45209.5). Because TODAY() returns the date precisely at midnight (45209.0), the exact mathematical comparison fails.
How can I easily check if my dates are stored as text?
Change the cell format to 'General'. If the date still looks like a standard date (e.g., '10/10/2023') instead of converting to a 5-digit serial number (like 45209), it is stored as text and needs to be converted.
Does the TODAY() function update automatically in conditional formatting?
Yes, the TODAY() function is volatile. It updates automatically based on your computer's system clock whenever the spreadsheet is recalculated, modified, or opened.
Can I use the DATEVALUE function to fix text dates?
Yes. If your date is stored as text, wrapping the cell reference in the DATEVALUE function (for example, =DATEVALUE(A2)=TODAY()) will convert the text string into a readable serial date for an accurate comparison.
