To all,

I have a worksheet where column A is a series of dates. These dates can
be in the format 2006/10/02 yyyy.dd.mm or 10/02/2006 dd.mm.yyyy. etc. I
want to be able to change them all so that they equal the dd.mm.yyyy
format. Is there anyway to do this using VBA, and not the cell format
options, which do not seem to work anyway.

Thanks in advance for your help,

regards

Joseph Crabtree

Sub changedateformat()

Sheets("sheet1").Select 'change the sheetname here
ActiveSheet.Range("a1:a20").Select 'change here which cells
you want to format
Selection.NumberFormat = "mm/dd/yy"
End Sub

You can either call this to run from another macro or create a command
button to call this when it is clicked.

Hi,

Im afraid that doesn't work. It keeps the dates exactly as they are.

Any ideas?

Thanks


On Dec 20, 2:22 pm, "keri" <keri.dow...@diageo.com> wrote:
> Sub changedateformat()
>
> Sheets("sheet1").Select 'change the sheetname here
> ActiveSheet.Range("a1:a20").Select 'change here which cells
> you want to format
> Selection.NumberFormat = "mm/dd/yy"
> End Sub
>
> You can either call this to run from another macro or create a command
> button to call this when it is clicked.

I have tested this on a list of cells with values like below;

2006/02/02
02/02/2006
04/01/01
01/01/2004

etc etc
and it works fine. The only ones it doesn't work on correctly are dates
entered with . instead of / or - so 2006.12.12 doesn't change.

What format are your dates in currently? Does it change any of the
dates or none? Did you change the sheet name in the code?

Hi,

Ive used a version of text to columns to format the dates into the
correct format. Thankyou very much for your help anyway.

Regards

Joseph Crabtree

On Dec 20, 2:44 pm, "keri" <keri.dow...@diageo.com> wrote:
> I have tested this on a list of cells with values like below;
>
> 2006/02/02
> 02/02/2006
> 04/01/01
> 01/01/2004
>
> etc etc
> and it works fine. The only ones it doesn't work on correctly are dates
> entered with . instead of / or - so 2006.12.12 doesn't change.
>
> What format are your dates in currently? Does it change any of the
> dates or none? Did you change the sheet name in the code?