Excel not properly comparing dates, despite same cell format

0
votes

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

3
exceldateexcel-formula
Is one date time and the other just date? Convert to number and see what the decimals are – urdearboy
Formatting doesn't matter when it comes to comparing values. Perhaps they have different time. When you click on each cell, the real value is shown in the function bar. What are the values? – DeanOC

3 Answers

1
votes

If you have confirmed that both are actually dates, and not text masquerading as dates, then time is most likely the issue. Note that time is represented via the decimal such that .5 equals noon.

Assuming you just want to know if the dates match independent of time you can use 

INT(G4) = INT(G6)
1
votes

If you want to compare just the dates use:

=Floor(G4,1) = Floor(G6,1)
0
votes

Format can mask the real cell value. Format both cells as General, then you can see the difference.

If a cell stays a "date" when it is formatted as General, then the cell is most likely text, not a date.

When formatted as General, you can clearly see if the number has any decimals, i.e. time on top of the date.