convert 13.11.2009 general to 13/11/2009 date, how to

[xppuser]

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]

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.

 

[tnazirov via OfficeKB.com]

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.