displaying dates

[bestox]

dear forum.

i got a huge amount of dates within this format "5/31/2002".
i use a german (speaking) keyboard plus operating system. This is why i would need the dates in this format "31.05.2002". Whenever i try to change the format (CTRL + 1) of the cell, f.e. customied or date, there is no change of the cell. When i do change the date from 5/31/2002 to 31/5/2002, the change of format takes effect, and i get a 31.05.2002 after changing the cell's format.

anyone knows, how to do that with 5/31/2002?

Thank s for your help!

 

[sanrv1f]

select the column containing the dates,

Data->Text to column->Click next->uncheck all delimiters->Click next->select 'MDY' in column data format->Click finish

now you can use custom format to change as 31.5.2009

 

[Sandeep Warrier]

dear forum.

i got a huge amount of dates within this format "5/31/2002".
i use a german (speaking) keyboard plus operating system. This is why i would need the dates in this format "31.05.2002". Whenever i try to change the format (CTRL + 1) of the cell, f.e. customied or date, there is no change of the cell. When i do change the date from 5/31/2002 to 31/5/2002, the change of format takes effect, and i get a 31.05.2002 after changing the cell's format.

anyone knows, how to do that with 5/31/2002?

Thank s for your help!

Going to the Custom Category and typing in dd.mm.yyyy did the job of converting the date to this particular format from various other types...

You need to check what your default format is. If your default format is dd/mm/yyyy, and you enter the date 5/31/2002, Excel might not recognize it as date and format it as text instead.

In the cell that contains 5/31/2002, what format is selected by default when u press Ctrl+1? Is Date selected or General/Text?

If that is the case, there are several ways to convert text dates to actual dates.

1. http://www.mrexcel.com/archive/Formatting/22921.html

2. http://office.microsoft.com/en-us/excel/HP012165021033.aspx

3. http://www.ozgrid.com/Excel/convert-excel-date-formats.htm <input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[bestox]

the solution was a combination of your both post s:

first i marked the "dates" and applied text to columns as sanrvf1 explained.
second i was editing the custom category, setting date to MM/TT/JJJJ.

work s fine.

thank you very much for your help!
this forum is simply great