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

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
93
Office Version
  1. 365
Platform
  1. Windows
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You could try Text to columns, delimited, next, clear all checkboxes, next, select MYD from the date drop down, Finish.
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top