matching data issue in if formula

Question

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!

Answer 1

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