1

I have two date rows. Both are formatted as dates. When I do a logic test to see whether the two dates, excel is not recognizing them as the same.

Here is a screenshot of the cells:

enter image description here

This is to confirm that both cell rows are formatted as date: enter image description here

This is to confirm that the equality check row is referencing the right cells:

enter image description here

I'm stumped. Does anyone have any idea what's going on here? Thanks

1
  • 1
    What if you change the format to Number? Do both show the same number? What is the source of the data? Is there any leading space anywhere before the date in one of the cells? What if you try VALUE(TRIM(CellRef)) and make a compare? e.g. =VALUE(TRIM(G4))=VALUE(TRIM(G6)) – patkim Mar 31 '20 at 21:27
2

The dates are equal only if the all portions of it, including TIME VALUES embedded in the date, are equal. I suggest to use TEXT() to compare only the date values. e.g:

=TEXT(G4;"YYYY-MM-DD")=TEXT(G6;"YYYY-MM-DD")

Note that the format codes to ACTUALLY use may be dependent on your locale (e.g. Swedes must use "ÅÅÅÅ" instead of "YYYY").

In your example; This =(G4-INT(G4))<>(G6-INT(G6)) most likely yields TRUE
(comparing the decimal portions)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.