"MN" wrote:
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 -----

"MN" wrote in message
...
Thank you all for reply ...
The data enter as a number (?) 1302005 for 01/30/2005 my data are in the
format of mmddyyyy. Tried enter 1302005 it become 1/1/1904 ?

"JoeU2004" wrote:

"MN" wrote:
I have a cell format as general like: 1102005 I want to convert to
format
I want to convert to format mm/dd/yyyy
I did try DATE, and DATEVALUE function but not working?
Like:
=DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4)))

Well, it might help if you looked at the Excel help page for DATE and
passed
the parameters in the correct order.

The parameters are DATE(year,month,day). Doesn't matter what format you
want (see later).

So try, assuming you have a typo and the date really looks lik 11022005:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))

and use the custom format mm/dd/yyyy if you want 01/01/2005, for example.
Or use the Date format if you want 1/1/2005.

Caveat: If you typed the date correctly above and it should be
interpreted
as 1/10/2005, post back for more instructions. That is, is the date
always
8-character text with leading zeros for the month and day? Or is the
entered as a number, which means that 1/1/2005 will appear as 1012005?


----- original message -----

"MN" wrote in message
...
Hi- I have been read all instruction to convert to date but none of
them
working so please help:
I have a cell format as general like: 1102005 I want to convert to
format
mm/dd/yyyy
I did try DATE, and DATEVALUE function but not working?
Like:
=DATE(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(R IGHT(A1,4)))
or
=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4))
:-((
Thanks you inadvange