Convert to date format dd/mm/yyyy

QuestionEdit

Please help me Urgently to convert date entered as 31.5.2010(entered as text) to 31/05/2010 i.e. convert dd.mm.yyyy to dd/mm/yyyy date format.

AnswerEdit

Nabam

=Substitute(A1,".","/")

if you want to actually convert it to a date

=DateValue(Substitute(A1,".","/"))

then format the cell as you want the date to appear.

if you want to do it inplace, then you would need to select the cells that have that type of value and use the Replace Command to replace . with /

in Excel 2007, the replace command is on the right side of the home ribbon

in earlier versions, it is on the Edit menu.