Converting Text to Date using "Text to Columns"
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....
--
Kahoko Tsuki.
"Stephen" wrote:
"kahoko" wrote in message
...
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 Tsuki.
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.
|