Hi

I have some dates like this:Sunday, 10 September 2006
in my worksheet, I've tried to change the format in excel with no luck.

If they can be changed would it be possible to run a macro to change all the
dates in the worksheet in one go.

Thanks in advance.

Dave

You didn't specify the format required but in a helper column, try:


=TEXT(TRIM(MID(A1,FIND(",",A1)+1,255)),"dd/mm/yyyy")

to convert to 10/09/2006

HTH

"DavidM" wrote:

Hi

I have some dates like this:Sunday, 10 September 2006
in my worksheet, I've tried to change the format in excel with no luck.

If they can be changed would it be possible to run a macro to change all the
dates in the worksheet in one go.

Thanks in advance.

Dave

Hi David

Click on the column letter containing the dates to mark the whole column
FormatCellsNumberCustom dd mmm yyyy
or whatever format you want to see

--
Regards

Roger Govier


"DavidM" wrote in message
. uk...
Hi

I have some dates like this:Sunday, 10 September 2006
in my worksheet, I've tried to change the format in excel with no
luck.

If they can be changed would it be possible to run a macro to change
all the dates in the worksheet in one go.

Thanks in advance.

Dave

You say that you've tried to change the format with no luck - this
implies that what you see as a date is really a text value - do you
have an apostrophe in front of the dates when you view them in the
formula bar?

If so, in a helper column you can enter the formula:

=VALUE(A1)

assuming the dates are in column A, and then you can format this cell
as you wish to have the date displayed. Copy this cell down your helper
column for as many dates as you have in column A. If you wish you can
fix the values in the helper column (using paste special) and then copy
them to overwrite the original "dates" and then delete the helper
column.

Hope this helps.

Pete

DavidM wrote:
Hi

I have some dates like this:Sunday, 10 September 2006
in my worksheet, I've tried to change the format in excel with no luck.

If they can be changed would it be possible to run a macro to change all the
dates in the worksheet in one go.

Thanks in advance.

Dave