MN said:
The data enter as a number (?) 1302005 for 01/30/2005 my data are
in the format of mmddyyyy.
More accurately, it is in the form of mddyyyy. The salient difference is:
the months 1-9 do not have a leading zero, and your number is not always 8
digits. Consequently, we cannot use LEFT(A1,2) and MID(A1,3,2) to exact the
month and day.
I am ass-u-me-ing that 2/3/2009 is represented by 2032009. Note the leading
zero before the day (03). Right?
If that's the case, then try:
=DATE(RIGHT(A1,4), LEFT(A1,LEN(A1)-6), MID(A1,LEN(A1)-5,2))
Be sure the column is wide enough for 10/30/2009 (10302009). If you see
"###", it is not wide enough.
Tried enter 1302005 it become 1/1/1904 ?
I don't see how you got that result. When I use my previous formula, which
is incorrect for 7-digit "data codes", I get the bogus result 1/2/2006.
That's to be expected since LEFT(A1,2) would use 13 for the month.
What cell is 1302005 in? And exactly what formula did you use to get
1/1/1904 (copy-and-paste from the Formula Bar)?
If you got 1/1/1904, I suspect: (a) you have the 1904 Date System option
set, or you are using a Mac; and (b) the cell that appears as 1/1/1904
actually contains a zero.
You do realize, don't you, that you should replace A1 in my formula with a
reference to whatever cell contains 1302005?
And IMHO, there is no good reason to set the 1904 Date System option (Tools
Options > Calculation) if you are using a PC (i.e. non-Mac). Some people
suggest it for dealing with negative elapsed time. I think it's a bad idea.
(No good reason, that is, perhaps unless you intend to use the xls file
directly on a Mac. I don't know beans about swapping files between the two
OSes.)
----- original message -----