convert 13.11.2009 general to 13/11/2009 date, how to
Dear all,
Office 2003, Windows XP Pro SP3 I have a column of dates in the following form 13.11.2009 (i.e. dd/mm/yyyy) where the cells' format are 'General'. I wanted to sort in ascending date order from 01/01/2009 to 31/12/2009. I tried formatting the cells to date and use the sort but what that did was to sort in this manner: 01.01.2009, 01.02.2009 - - - 01.12.2009 then 02.01.2009, 02.02.2009 i.e. by months first then by dates. Even when I have chosen dd/mm/yyyy formatting, not only the content won't change the sort still perform as I have just described. I have also tried the formula =DATEVALUE(DAY(A1) & "/" & MONTH(1) & "/" & YEAR(A1)) without success (it returns VALUE! error in the adjacent column) I would appreciate therefore if someone could advice me how to change the dd.mm.yyyy (for which the cells are formatted 'General') in a new column to dd/mm/yyyy. Thank you, jes |
convert 13.11.2009 general to 13/11/2009 date, how to
Select the column
Data|Text to columns (in xl2003 menus) Follow the wizard, but choose Date (dmy order). and finish up. Change the format of the column to an unambiguous date format: mmmm dd, yyyy to see if your dates were converted ok. If they were, then format the way you like. xppuser wrote: Dear all, Office 2003, Windows XP Pro SP3 I have a column of dates in the following form 13.11.2009 (i.e. dd/mm/yyyy) where the cells' format are 'General'. I wanted to sort in ascending date order from 01/01/2009 to 31/12/2009. I tried formatting the cells to date and use the sort but what that did was to sort in this manner: 01.01.2009, 01.02.2009 - - - 01.12.2009 then 02.01.2009, 02.02.2009 i.e. by months first then by dates. Even when I have chosen dd/mm/yyyy formatting, not only the content won't change the sort still perform as I have just described. I have also tried the formula =DATEVALUE(DAY(A1) & "/" & MONTH(1) & "/" & YEAR(A1)) without success (it returns VALUE! error in the adjacent column) I would appreciate therefore if someone could advice me how to change the dd.mm.yyyy (for which the cells are formatted 'General') in a new column to dd/mm/yyyy. Thank you, jes -- Dave Peterson |
convert 13.11.2009 general to 13/11/2009 date, how to
If at you a format date is dd/mm/yyyy data of a kind 13.11.2009 are not for
Excel date. Therefore Excel sorts data of a kind 13.11.2009 not as date, and as text. I see 2 decisions of the given problem: 1. To change all data from a kind 13.11.2009 by sight 13/11/2009 using a command replace (Ctrl+F); 2. To change a format date with dd/mm/yyyy on dd.mm.yyyy using Control Panel. xppuser wrote: Dear all, Office 2003, Windows XP Pro SP3 I have a column of dates in the following form 13.11.2009 (i.e. dd/mm/yyyy) where the cells' format are 'General'. I wanted to sort in ascending date order from 01/01/2009 to 31/12/2009. I tried formatting the cells to date and use the sort but what that did was to sort in this manner: 01.01.2009, 01.02.2009 - - - 01.12.2009 then 02.01.2009, 02.02.2009 i.e. by months first then by dates. Even when I have chosen dd/mm/yyyy formatting, not only the content won't change the sort still perform as I have just described. I have also tried the formula =DATEVALUE(DAY(A1) & "/" & MONTH(1) & "/" & YEAR(A1)) without success (it returns VALUE! error in the adjacent column) I would appreciate therefore if someone could advice me how to change the dd.mm.yyyy (for which the cells are formatted 'General') in a new column to dd/mm/yyyy. Thank you, jes -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
All times are GMT +1. The time now is 11:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com