Date Problem

E

Ed

I pull data into excel that contains date that look like this....10104
which is supposed to be Jan 4th 2001 (YYMMDD)
I there is a simple way to convert this to a readable date? Any format is
fine, whether it's yyyymmdd or mmddyy or whatever.

T.I.A.
Ed
 
D

Debbe

Select the cells with the date
Go to Format...Cells...Number tab...select date from the "Category" lis
then select the format you want from the "Type" list
 
E

Ed

That doesn't work. The problem is this number 10104 is only a 5 digit
number. Excel tries to convert that number to a date of 8/30/27
 
J

Jonathan Rynd

I pull data into excel that contains date that look like this....10104
which is supposed to be Jan 4th 2001 (YYMMDD)
Click to expand...

Assuming that the year is always one digit (dates in the range 2001-2009)

=DATE(2000+LEFT(A1,1),MID(A1,2,2),MID(A1,4,2))

If the year might someday become two-digits:
=DATE(2000+LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-4,2),MID(A1,LEN(A1)-2,2)
 
Top