Odd Excel Date Issue

[kenderweasel]

Hi,

I'm havin a problem finding the latest date in a column. In column B, I have an imported dataset which puts the date in DD-MMM-YY hh-mm-ss-aaaa... In column A, I have selected the first 9 digits using the LEFT function, and returned the DATEVALUE: =DATEVALUE(LEFT(B3, 9)). I have also formatted the column to Date format.

However, when I try to return the latest date from column A, I am either getting a VALUE error, or a (seemingly) random date returned. The formulae I have tried are:

=MAX(A:A)
returns #VALUE error
=MAX(DATEVALUE(A2))
returns #VALUE error
=LARGE(LEFT($B:$B,9),1)
returns random date

https://www.dropbox.com/s/ivqixwcb5y39imq/DUMMY DATA.xlsx?dl=0

Thanks

 

[Fluff]

If you remove all the formulae in col A from row 777 onwards(which give #Value ) then your 1st formula will work

 

[kenderweasel]

I feel like such an idiot lol - thank you so much for your help!

 

[Fluff]

We've all been there

Glad to help