Hard Code to Date Formula?

K

Ken

I am pasting an Access Query into Excel 2000 ... One of
the Fields contains a 6 character value ... Access format
of Field unknown ... However, value represents a DATE.

Above said ... Excel is seeing format as "General" & sort
(ascending) is not working as 02 Dates are sorting to
bottom behind 03 ... I am assuming this is due to the
Access format, but for now must attempt fix in Excel.

ie:

120102 ... Value represents 12/01/02
122702 ... etc
101502 ... etc
032003 ... etc
082503 ... etc
110603 ... Value represents 11/06/03

How do I get Excel to convert & treat as a mm/dd/yy Format.

Thanks ... Kha
 
J

Jerry W. Lewis

If 120102 is in A1, then
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))
will return 37591, which when formatted as a date is Dec 1, 2002.

Jerry
 
G

Guest

-----Original Message-----
If 120102 is in A1, then
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))
will return 37591, which when formatted as a date is Dec 1, 2002.

Jerry


.
Click to expand...
 
Top