Q. How can I change this date

[DavidM]

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

 

[Guest]

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

 

[Roger Govier]

Hi David

Click on the column letter containing the dates to mark the whole column
Format>Cells>Number>Custom> dd mmm yyyy
or whatever format you want to see

 

[Pete_UK]

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