Text to Date

[MN]

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(RIGHT(A1,4)))
or
=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,4))
:-((
Thanks you inadvange

 

[Jacob Skaria]

Select the range of dates which needs to be corrected. From menu Data>Text to
Columns will populate the 'Convert Text to Columns Wizard' Hit Next>Next will
take you to Step 3 of 3 of the Wizard. From Column Data format select Date
and select the date format in which your data is (DMY).Hit Finish. MSExcel
will now convert the dates to the default date format of your computer.

If this post helps click Yes

 

[JoeU2004]

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(RIGHT(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 -----

 

[T. Valko]

On my copy of Excel 2002 that only works if the string of digits are 8
characters long.

It works for this:

11022005

It doesn't work for this:

1102005

 

[MN]

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]

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 -----

 

[T. Valko]

This might work:

=--TEXT(A1,"00\/00\/0000")

Works on 7 or 8 digit strings

 

[MN]

Thank you it work !!!

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.



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
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 -----

 

[JoeU2004]

Thank you it work !!!

Good to hear. You're welcome. But I hope you saw Biff's (T Valko). It is
much more elegant and efficient without sacrificing intuitiveness.


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