Finding the largest date in "mm.yyyy" format in a text (?) column.

[eirikdaude]

I have a spreadsheet in which I dump some data from SAP, which ends up looking something like this

I then insert a formula in J2 which is supposed to find the latest date / month there is a registration for. However, it doesn't seem to be able to interpret values in column D as dates.

What would be the simplest way to implement this? I'd be fine with using VBA for this, but simpler is better.

I believe the month will always be represented by the leftmost two digits in the cells, but I'm not positive that e.g. 1.2021 might not pop up.

 

[Fluff]

You could try Text to columns, delimited, next, clear all checkboxes, next, select MYD from the date drop down, Finish.

 

[eirikdaude]

Using DateValue to convert the values in the column fixed the issue for me - i.e.

=MAX(DATEVALUE(D2:D13542)))

gives me the value I want.

 

[Fluff]

Glad you sorted it & thanks for the feedback.