Excel cloumn sorting

[Jim624]

I need to sort a column by date......Year, Month etc. However the former bookkeeper set up the date format different than what I use. I want to know if there is a way to get all the dates into the correct order.. The column has dates like 4/17 and April 2017 and 8/20 and Dec 2019 and 12/20. How do I sort these, there is no way I can go back and change each date.....cutomer list is 8900 customers long

[teylyn]

Hello,

are these dates really dates? When you format the cells with a different number format, like "General", do they turn into numbers? If not, these are not dates but text. Sorting will not deliver the desired results. You will need to turn them into real dates. This will probably not be easy to automate. It would be good if you could post a data sample. Copy a few hundred rows from just the Date column into a new file. Save that and upload here. Click the "Go Advanced" button at the bottom and then the paper clip icon to attach a file.

cheers, teylyn

[Jim624]

here ya go........

[FDibbins]

Looking at what that file contains, all I can say is - wow, really? lol

At a quick glance, I can see...
entries with multiple leading spaces
entries with no leading spaces
mm/yyyyy with no day
mmm/yyyy with no day
m/ddd with no year
Season yyy
plain text entries

Not 1 of those entries is actually a real date (by excel standards)

This is going to take some serious cleaning up, just to get it to a usable format

[Jim624]

The company is a septic cleaning service so the day of the month is not important. The last bookkeeper was with the company for 15 years.....and she was sick alot.....soo they had other people come in and input data hence the mixed up spaces and such.

[FDibbins]

OK, but what about the dates with no year - and the text entries?

[Jim624]

if it shows 6/17 that means June of 2017.....

[FDibbins]

OK, this will get you dates for most of what you have there - I put this in D3 and copied down...
=IFERROR(DATE(20&MID(B3,FIND("/",B3,1)+1,2),LEFT(B3,FIND("/",B3,1)-1),1),DATEVALUE(B3))

Look down the list for the ERRORS, and correct the entry in column B if you can...some are simple typos (Oxct 2019... Set 2019 etc)

Once you havethat sorted out, you can either just keep those formulas there, or copy/paste values to replace the original data and delete the formulas