Fix an Excel IF Date Comparison That Always Returns FALSE
Question details
The user needs to fix an IF formula that returns FALSE even though two displayed dates appear to match.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing a date in a worksheet cell with a date value in an AnnualLedger table or range.
- Observed behavior
- The IF comparison returns FALSE because one or both dates may be stored as text, include different time values, or come from incompatible ranges.
Check the formula bar for both date cells, because identical date formatting does not confirm that their underlying values are the same. Test each date with ISNUMBER before changing the comparison formula.
Convert both compared values to real dates and remove hidden times
Use this solution when dates look identical on the sheet but an IF equality test returns FALSE.
Excel stores valid dates as serial numbers. A text date cannot equal a real date value, and a date with a hidden time such as 12:00 PM will not equal the same date at 12:00 AM.
In an empty cell, enter =ISNUMBER(C5). Then test the table value with =ISNUMBER(AnnualLedger[@CombinedDate]). TRUE means the value is stored as a numeric date; FALSE means it is text or another non-date value.
If ISNUMBER returns FALSE, enter =DATEVALUE(C5) for text containing only a date, or =VALUE(C5) when the text may include a time. Copy the result and use Paste Special > Values to replace the original value if needed.
If both values are real dates but may contain times, compare the date-only serial values with =IF(INT(C5)=INT(AnnualLedger[@CombinedDate]),"Match","No match").
When both source cells contain date-only numeric values, use =IF(C5=AnnualLedger[@CombinedDate],"Match","No match") and fill the formula down within the table row.
Use a lookup formula to return the matching table value
Use this approach when the goal is to find the row in AnnualLedger that has the same date as C5, rather than compare only the current table row.
Compare and Match Dates Reliably in WPS Spreadsheet
WPS Spreadsheet lets you test date values, remove time components, and use IF, XLOOKUP, or INDEX/MATCH formulas to compare dates and return related table data. Its familiar spreadsheet layout makes it easy to move existing Excel workbooks into WPS Office.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the affected .xlsx or .xls workbook.
- 2. Check the date storage type: Select an empty cell and enter =ISNUMBER(C5) to determine whether the first compared value is a real date number.
- 3. Normalize dates before comparison: Use =INT(C5) and =INT(AnnualLedger[@CombinedDate]) in helper cells when hidden time values may be included.
- 4. Enter the matching formula: Use =IF(INT(C5)=INT(AnnualLedger[@CombinedDate]),"Match","No match") for a row comparison, or enter XLOOKUP to retrieve data from the matching table date.
Frequently Asked Questions
Why do two dates that look the same return FALSE in an IF formula?
The values may not be the same underlying serial number. One may be text, or one may include a hidden time. Use =ISNUMBER(cell) to check the storage type and =INT(cell) to compare date-only values.
How can I tell whether an Excel date is stored as text?
Enter =ISNUMBER(C5) in a blank cell. FALSE indicates that C5 is not stored as a numeric date. Convert date text with =DATEVALUE(C5), or use =VALUE(C5) if the value can include time information.
Does changing the date format fix an IF date comparison?
No. Home > Number Format changes only the displayed appearance. It does not convert text into a real date or remove a time value stored with the date.
How do I ignore time when comparing two dates?
Wrap both references in INT, such as =IF(INT(C5)=INT(D5),"Match","No match"). INT removes the fractional time portion from an Excel date-time serial number.
Which formula should I use to find a matching date in another table?
Use =XLOOKUP(C5,AnnualLedger[CombinedDate],AnnualLedger[Text],"") for an exact match. If XLOOKUP is unavailable, use =IFERROR(INDEX(AnnualLedger[Text],MATCH(C5,AnnualLedger[CombinedDate],0)),"").
