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
--
Gary's Student


"Heven" wrote:

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

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

On Tue, 28 Nov 2006 17:01:02 -0000, Gary''s Student
wrote:

If they are all dates and not text, then select them and pull-down:

Format Cells... Number Custom dd/mm/yyyy

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.

"Heven" wrote:

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 you send me a document dated 01/12/2006, I will read that as Jan 12, 2006
when you meant 01 Dec 2006.

If I enter 1-3-6 or 1/3/6 (doesn't work with periods, keep bat handy) into a
cell formatted as "dd mmm yyyy" it appears as 03 Jan 2006. To anyone that
reads their document while typing an error is apparent. Depending on your
regional settings 1-3-6 input into a cell formatted "dd mmm yyyy" may appear
as 01 Mar 2006 but; if you read while typing and that isn't what you want it
is obvious.

Consider assigning a "Data" "Validation" message (appears when cell
selected) with the date format of your choice.
Lou

"Rookie 1st class" wrote:

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.

"Heven" wrote:

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