Finding latest date from range not working

[sharky12345]

I have a range of dates formatted as 'dd/mm/yyyy' and I'm trying to get the latest date using this formula;

=MAX(E2:E150000)

It's not working though - it has given me the latest date as '31/08/2018' when there are other cells with dates as '15/09/2018'.

I've checked that the range is referenced correctly and all of the dates in the range are the same format, so what's going wrong?

 

[Joe4]

What cell is your '15/09/2018' entry in?

Let's say it is E10. Then tell me what this formula returns:
=ISNUMBER(E10)

 

[sharky12345]

Hi - formula returns TRUE

 

[Joe4]

Hi - formula returns TRUE

I don't see how that is possible then. That confirms that is a valid date entry.

If I enter those two dates in cells A1 and A1, and then do:
=MAX(A1:A2)
I get the September date every time.

Can you try doing that yourself in a blank sheet and confirm?

The only way it would return the smaller date is if:
- The date you are checking is actually outside of the range in your formula
- The entry is actually a text entry

Which cells exactly do these two dates appear?
They aren't part of any merged cells, are they?

 

[sharky12345]

Thanks Joe4, I found the problem - the date had been entered incorrectly as '31/08/2918' - DOH!

 

[Joe4]

Thanks Joe4, I found the problem - the date had been entered incorrectly as '31/08/2918' - DOH!

Yep, that would do it.
Did you just overlook that, or was the date format really "dd/mm/yy" so you only saw two digits of the year?

 

[sharky12345]

No, that was deliberate but good spot!