Excel file has mixture of dates as text and serial numbers

Question

I've gotten an Excel file in which some of the dates are actually serial dates (42741, 42800, etc.), and some are just text. Since the serial dates are formatted as dates, they all look alike, but Excel can't correctly sort them, and displays them as spanning a year instead of the two month period they actually represent.

To make things worse, the dates are formatted in the US style (mm/dd/yyyy), and my system uses the International (dd/mm/yyyy).

How do I clean this data!?

Answer 1

After much grief, this is the method that worked for me. I hope it might help others!

1. Add a length column, containing =LEN(your date). The serial dates will have 5 characters, while the text ones will have 7. Sort by length.

2. Filter for the 5-length (serial) dates, and copy them to an adjacent column.

3. Use the text-to-column feature on these cells. Separate by delimited, under delimiters choose other, and type "/" (or ".", if relevant). Hit finish.

4. In an adjacent column, use the DATE function to reconstitute your date, month, year pairs into a date. (Note that if your year just says, say, "17", Excel will assume it to be 1917, so either add the leading "20" or hard-code it.)
5. Now, do a similar process with your 7-digit dates - copy them, split by "/", (or "."), recombine into dates.
6. Copy your new dates back to their original slots and format them as "Short date". Et voilà! Excel now recognises them as dates. You can sort and filter by actual date.