CODEWITHSUNDEEP
exceldatesortingformat
Carol Swartz
Carol Swartz

Reputation: 11

Excel won't wort mixed dates

I downloaded a file from a government source. There is a column of dates in mixed formats. Some appeared 12-25-99 while others were 25-Dec-99 and some were 12/25/99. I want to sort them newest to oldest. There are over 600,000 records but I tried everything on a sample of about 25 to save time.

I managed by brute force to get them all looking the same: 12/25/99. There is nothing I can do to get them to sort. Some of the list sort because they were all the same format to start with, generally 12-25-99 though it might have been 25-12-99. No matter. The ones that had slashes never integrated with the ones that started out with dashes.

I found 6 or 7 ideas on this site but none of them worked. Help if you think you can.

Thanks.

Upvotes: 1

Views: 38

Answers (1)

teylyn
teylyn

Reputation: 35990

The data samples you list suggest that what you perceive as "dates" is at least partly text. Excel internally stores dates as numbers, with day values before and time values after the decimal point (or decimal comma, depending on your regional settings). These numbers can now be formatted to appear as dates.

To check which of the "dates" are really dates, select them all and change the format to "General". Any real dates will now show as numbers, whereas any "dates" that are really just text, will remain unchanged.

In order to sort this data chronologically, you will need to convert the text values to real dates.

There are several different approaches you could use for that. You could use a formula like DateValue() - this works well with text that has the date and month in the same order as your regional settings. So, if you use US settings with MDY order of a date, give that a try.

Otherwise, and especially if the original dates are a mix of DMY and MDY order, you will need to use more sophisticated data cleanup techniques, like Text To Columns or Power Query.

Upvotes: 2

Related Questions