Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format date to e.g. 2009.08.31 | Excel Discussion (Misc queries) | |||
Convert Excel to Quicken 2009 | Excel Discussion (Misc queries) | |||
How do I change a date fro 22 sept 09 to September 22, 2009 | New Users to Excel | |||
how can subtact or add Week 07/2009 in one column & Week 10/2009 . | Excel Worksheet Functions | |||
How create 365 labels for 2009? Example: Monday, Jan. 4, 2009 | Excel Discussion (Misc queries) |