matching data issue in if formula

crib bar 781 Reputation points

I am trying to compare two columns of data, which are formatted as dd/mm/yyyy to check they match, using a very simple IF formula: =IF(A2=B2,"match","not")

Visually those which match are producing a "not" result, even though the date is identical in both cells (visually). Are there any obvious reasons for this. I have double checked that Excel recognises them both as dates. For what its worth, the data I am comparing was extracted from 2 different systems, one was an Access database and the other was from another database platform.

I've even copied and pasted a sample of data into another spreadsheet and re-run the same check and again I am getting unexpected not results for those that visually are the same. Is there anything I can do to 'cleanse' the data before re-running the match? Or any other reasons you can think of why they are not matching up?

Maybe IF is not an ideal function for comparing date fields. As a supplementary check (to confirm I am not losing it!) I also did a =A2-B2 and they return 0 suggesting they are the same, but the IF formula doesnt seem to agree!

A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,619 questions
{count} votes

Accepted answer
  1. Viorel 114K Reputation points

    Check this formula: =IF(TRUNC(A2)=TRUNC(B2),"match","not").

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful