date formats

[Roy]

Hi All
I have a date in text format like this:
11-Sep-98
I would like to convert it to this:
09/11/98

I have tried with cell formating but it doesn't work, is there a formula to
do it?
TIA
Roy

 

[Trevor Shuttleworth]

Roy

select all the cells with dates; then:

Format | Cells... | Number tab | Category : Custom | and Type : mm/dd/yy
as your format

If this doesn't work, check that you have a date(s) in the cell(s). Format
as general and if it still looks like a date, it's not - it's text. If it
is text, you probably need to do a replace all "-"'s with "/"'s and all
spaces with nulls

Regards

Trevor

 

[Ken Wright]

Select the column of dates and do Data / Text To Columns / Delimited / Next,
Next, Tick Date and choose format and hit OK or finish.

 

[JE McGimpsey]

If changing the cell format doesn't change the display, then it's likely
that your "dates" are really Text (happens frequently, especially when
data is pasted in from external sources).

Copy a blank cell. Select your dates and choose Edit/Paste Special,
selecting the Values and Add radio buttons. Click OK. This will coerce
text dates to numeric (real) dates.

 

[Bob Phillips]

Roy,

In an adjacent cell, add this formula and copy down =DATEVALUE(A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

Roy

Data>Text to Columns>Fixed Width>Next>Next "column data format">Date>DMY

Re-format to dd/mm/yy

Gord Dibben Excel MVP