Solved How to convert text lines of differing length to DateTime?

February 9, 2016 at 13:28:43
Specs: Windows 7
I am using Excel 2010.

The format of the dates from the raw data file (a delimited text dump that I import to excel):
mddyyyy OR mmddyyyy depending on which month.

So January 1st 2015 would look like: 1012015
Whereas October 1st 2015 would look like: 10012015

Formatting the column as Date leaves things either incorrect or unreadable (#########).

I've tried using a formula to pull the specific characters out and format them (LEFT(A1, 2)&"/"MID(A1, 3, 2)) etc. but that doesn't work since the length of the string can change at any time.

Would there be a simpler/faster way to do this besides going through every incorrect date and manually fixing it? The database has thousands of records.

message edited by cornycotton


See More: How to convert text lines of differing length to DateTime?

Report •


#1
February 9, 2016 at 14:03:29
I have no experience with calculations or programming in Excel,
just with programming in general...

Can't you determine the length of the string to decide whether to
parse the beginning as a single-digit month or a two-digit month?

Can't you parse the string from right-to-left? First year, then date,
and finally month?

If you have a standard format, and only a few thousand records
in total, and you plan to keep using this database for some time
(or transfer the data to another database), then the best idea may
be to edit the nonstandard dates.

-- Jeff, in Minneapolis


Report •

#2
February 9, 2016 at 14:27:52
✔ Best Answer
As Jeff suggested, use the length to determine how to cut up the date.

Try this:

=IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,2,2)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)))

MIKE

http://www.skeptic.com/


Report •

#3
February 9, 2016 at 14:38:03
This was very helpful!

Report •
Related Solutions


Ask Question