How to Compare Excel Values Ignoring Date Formats and Text Types
Question details
The user needs to reliably compare data entries representing identical dates or numbers that are formatted differently, contain varying punctuation such as dashes, or mix text and date data types, particularly when handling historical dates before the year 1900.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two columns of dates or numerical values where the visual formatting or underlying data types differ across the cells.
- Observed behavior
- Standard comparison formulas return FALSE for equivalent values due to mismatched formatting (e.g., "09 Aug 1945" vs "9-Aug-1945") or differing storage types (serial date vs text).
Ensure your columns are wide enough to display the full text, and insert blank helper columns next to your data to hold the normalized values.
Normalize and Compare Standard Dates Using Formulas
Use text and value functions to convert differing date formats into a uniform structure, ignoring extra dashes and spaces, before comparing.
When Excel compares a serial date value to a text string, it treats them as mismatched data types even if they look identical. Normalizing the punctuation and converting both entries to the same data type will resolve this issue.
Create a helper column and use the SUBSTITUTE function to remove dashes or standard separators. For example, enter `=SUBSTITUTE(A2, "-", " ")` to replace dashes with spaces.
Wrap your formula in the VALUE function to force Excel to read the text string as a standard numeric serial date: `=VALUE(SUBSTITUTE(A2, "-", " "))`.
Apply the same date format to both your original target column and the new helper column via the 'Format Cells' dialog.
In a final column, use the equals operator (e.g., `=B2=C2`) to compare the cleaned, standardized values. It will now correctly return TRUE.
Handle and Compare Dates Before January 1, 1900
Since spreadsheet software does not recognize pre-1900 dates as serial numbers, split these historical dates into individual components for accurate comparison.
Easily Normalize and Compare Data in WPS Office
WPS Spreadsheet provides powerful data formatting tools and text functions that make comparing mixed-format data incredibly simple. Seamlessly fix mismatched dates and text formats without changing your workflow.
- 1. Open your data: Launch WPS Spreadsheet and open the workbook containing your mixed-format date columns.
- 2. Insert helper columns: Right-click the column header next to your data and insert a new blank column to hold your normalized text.
- 3. Apply cleanup formulas: Use the `=SUBSTITUTE()` function to remove extra dashes and spaces that are causing comparison errors.
- 4. Compare values instantly: Use a simple logic formula like `=A2=B2` to check for exact matches between your freshly cleaned data columns.
Frequently Asked Questions
Why does Excel return FALSE for dates that look exactly the same?
This happens when the data types do not match. One cell might be formatted as a numeric serial date while the other is stored as a text string, or there may be hidden spaces or mismatched punctuation like dashes.
How do I ignore dashes when comparing two cells in Excel?
You can use the SUBSTITUTE function to temporarily remove dashes during the comparison. For example, use `=SUBSTITUTE(A1,"-","")=SUBSTITUTE(B1,"-","")` to compare the underlying characters without the dashes.
Why can't standard formulas process dates from the 1800s?
Excel operates on a calendar system that begins on January 1, 1900. It cannot calculate or recognize dates before this baseline as serial numbers, so any 1800s dates are treated purely as text.
