Converting Text to Date using "Text to Columns"

  • Thread starter Thread starter kahoko
  • Start date Start date
K

kahoko

Hi all,

I have a few columns of date in excel which is imported from external data
source. It is shown as text in Excel. Some sample of the data are like
- 240708 (which is 24th July 2008)
- 10208 (which is 1 Feb 2008)

However, when I use the Text to Columns/Date(DMY) function to convert them
date, the following results is given
- 240708 --> 24/07/08 (correct)
- 10208 --> 2/10/08(INCORRECT)

I tried with Text to Columns/Date(MDY), then
- 240708 --> 240708 (nothing happen)
- 10208 --> 1/02/08(correct)

Is there any method in Excel I can use to convert this text to date? They
come in a mix of 5 and 6 digits.

Please help...

Thank you.
 
kahoko said:
Hi all,

I have a few columns of date in excel which is imported from external data
source. It is shown as text in Excel. Some sample of the data are like
- 240708 (which is 24th July 2008)
- 10208 (which is 1 Feb 2008)

However, when I use the Text to Columns/Date(DMY) function to convert them
date, the following results is given
- 240708 --> 24/07/08 (correct)
- 10208 --> 2/10/08(INCORRECT)

I tried with Text to Columns/Date(MDY), then
- 240708 --> 240708 (nothing happen)
- 10208 --> 1/02/08(correct)

Is there any method in Excel I can use to convert this text to date? They
come in a mix of 5 and 6 digits.

Please help...

Thank you.
Click to expand...

I can't reproduce your results (using Excel 2000). For me, Text to
Columns/Date(DMY) converts both correctly. Are you sure there are no
invisible characters along with the 10208 data? Try a formula such as
=LEN(A1) to check that the length of the string really is 5.
 
Hi Stephen,

Thank you for responding. Am using Excel 2003.
I have tried using formula such as Len() as well, its really 5 characters.
As I even tried to use IF() to check if length is 5, append a 0 in front.
However, with formulas in cells, the Text to Columns fucntion don't seem to
work.

The Columns/Date(DMY) keeps displaying 10208 as 10th Feb. Whereby it
actually is 1st Feb.

Result Original
16-Jan-08 160108
10-Feb-08 10208
26-Mar-08 260308
24-Jun-08 240608

Am running out of ideas here....
 
Would it help to force all data to be 6 characters long? You could try it
manually for one cell, by entering '010208. If this works, you could use a
formula, copied down, to add the leading zero to 5 character cells:
=IF(LEN(A1)=5,"'0"&A1,A1)
(note the ' inside the " " and before the 0),
or
=IF(LEN(A1)=5,"'0"&A1,"'"&A1)
which forces all entries to be text.

The other thing to check is your date settings in Windows Regional Settings.
Both long and short versions should be UK or US style, not one of each. I
just mention this because I notice that the dates that do convert properly
all have days greater than 12, so couldn't be interpreted as dates of the
'other' style. In this respect, 10208 is ambiguous. I don't know why this
would cause it to go wrong - it's just an observation and a possibility for
solving your problem.
 
Hi Stephen,

Thanks again. Have solved the date formating issue using VBA in Excel.
Similar logic to what you suggested, change the length to 6 character text
then format them to DATE.

=)
 
Hi all,

I have a few columns of date in excel which is imported from external data
source. It is shown as text in Excel. Some sample of the data are like
- 240708 (which is 24th July 2008)
- 10208 (which is 1 Feb 2008)

However, when I use the Text to Columns/Date(DMY) function to convert them
date, the following results is given
- 240708 --> 24/07/08 (correct)
- 10208 --> 2/10/08(INCORRECT)

I tried with Text to Columns/Date(MDY), then
- 240708 --> 240708 (nothing happen)
- 10208 --> 1/02/08(correct)

Is there any method in Excel I can use to convert this text to date? They
come in a mix of 5 and 6 digits.

Please help...

Thank you.
Click to expand...


=DATE(MOD(A1,100)+1900+100*(MOD(A1,100)<31),MOD(INT(A1/100),100),INT(A1/10000))


--ron
 
Back
Top