Date and time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

10th March 2006
15.03.08
2001.04.30
feb 2006 23

how to change all the above date formats in to a single format as -
dd/mm/yyyy
 
If they are all dates and not text, then select them and pull-down:

Format > Cells... > Number > Custom > dd/mm/yyyy
 
Presumably they are not dates with different formats (in excel)

As the poster mentions the dd/mm/yyyy in the question, they obviously know
the
Excel custom format feature :)

So I'll assume the various examples have been entered and need turning
into dates
Can't see any easy way, each different type needs to be split putting day,
month and year into 3 helper columns.
Try using string splits for 15.03.08 and 2001.04.30
In certain cases those last 2 formats can be converted using "Date -> Text
to Columns" and using the Date option

You'll need a lookup to convert March, Feb etc into a Month number - for
the =date()
Then resconstruct the excel data from these, using Date(year,month,day)
This last cell can then be formatted dd/mm/yyyy

Steve
 
Louisville slugger applied to knuckles of offending parties. Then Format date
column containing dates as "Custom" dd mmm yyyy (IE. 30 Nov 2006) to
alleviate confusuion for all.
 
Back
Top