date formats

  • Thread starter Thread starter Roy
  • Start date Start date
R

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
 
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
 
Select the column of dates and do Data / Text To Columns / Delimited / Next,
Next, Tick Date and choose format and hit OK or finish.
 
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.
 
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)
 
Roy

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

Re-format to dd/mm/yy

Gord Dibben Excel MVP
 
Back
Top