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

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
I have a spreadsheet in which I dump some data from SAP, which ends up looking something like this

1612528503551.png


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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
You could try Text to columns, delimited, next, clear all checkboxes, next, select MYD from the date drop down, Finish.
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
Using DateValue to convert the values in the column fixed the issue for me - i.e.

Excel Formula:
=MAX(DATEVALUE(D2:D13542)))

gives me the value I want.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top