Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Text to Date using "Text to Columns"
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Text to Date using "Text to Columns"
"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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Text to Date using "Text to Columns"
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. "kahoko" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Text to Date using "Text to Columns"
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. =) -- Kahoko Tsuki. "Stephen" wrote: 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. "kahoko" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Text to Date using "Text to Columns"
On Tue, 27 Nov 2007 23:04:00 -0800, kahoko
wrote: 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. =DATE(MOD(A1,100)+1900+100*(MOD(A1,100)<31),MOD(IN T(A1/100),100),INT(A1/10000)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a text form of "=Sheet2!A1" into a reference formula | Excel Discussion (Misc queries) | |||
Formating numbers &"Text" to appear as currency &"Text" in formula | Excel Discussion (Misc queries) | |||
Excel should support a proper inverse to "Text to columns" | Excel Discussion (Misc queries) | |||
Splitting text, like "Text to columns", but as a formula | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |