CODEWITHSUNDEEP
exceldateexcel-formula
Josh Friedlander
Josh Friedlander

Reputation: 11657

Excel file has mixture of dates as text and serial numbers

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!?

Upvotes: 1

Views: 751

Answers (3)

JB-007
JB-007

Reputation: 2461

Below/here refer to resolve in 2 steps:

Step 1: fix serial dates

=--DATE(YEAR(B2:B9),DAY(B2:B9),MONTH(B2:B9))

Step 1 - fixing serial values

Step 2: fix text formatted dates (i.e. errors in Step 1)

=IFERROR(C2#,DATE(2000--RIGHT(B2:B9,2)-100*(--RIGHT(B2:B9,2)>50),
--MID(B2:B9,1,SEARCH("/",B2:B9)-1),--MID(B2:B9,SEARCH("/",B2:B9)+1,2)))

Step 2 - fixing text string

*Notes:

  • Handles both 'yyyy' and 'yy' year format types in orignal data text version
  • '00-'49 will map to 2000-2049 resp., and 50-99 map to 1950-1999 resp if 'yy' is only avail. in original data (text variant of 'date'). Can alos be customized as required.

Context: This often happens when dates have been output in US (mm/dd) format but Excel interprets as UK (dd/mm). There is a potential 'Y2K' bug / issue - if text formatted dates have the full 4 character year, then you can simply use Date(--right(b2:b9,4),

Upvotes: 1

Forward Ed
Forward Ed

Reputation: 9874

lets pretend your dates are in Column A place the following in an adjacent column and copy down accordingly.

=IF(ISNUMBER(A2),L7,DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,FIND("/",A2)+1,2)))

Alternative use TEXT to Column built in function in the ribbon.

1)  Select your range of mixed dates
2)  Select Text-to-Columns
3)  Keep selecting next until you come to the page after selecting delimiters
4)  Choose your destination location (default is overnighting your data)
5)  Select the column header in the preview
6)  Select the Date radio button for format
7)  Select the date format that matches the source format

Serial dates will remain unchanged, and text dates will be converted.

Upvotes: 2

Josh Friedlander
Josh Friedlander

Reputation: 11657

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.

Upvotes: 1

Related Questions