Text to columns (date conversion error) while using MACROs

Question

M trying to convert a date column (eg 31/12/2010 format) into Dec-2010 format.

I use the text to column function in excel 2007.

Whilst recording the macro, it converts everything perfectly, including the date requirement.

However, when I run the macro on a fresh set of text, it converts the text to columns, but some - not all - of the date fields swap the day and month numbers around (i.e. as in the US date display), whilst the remaining dates revert back to text.

An extra catch is that the sheet reads the cells in the european date configuration, so the day becomes the month and the month becomes the day.

e.g. 31/12/2010 - displayed as 12/2010  when working properly, becomes 31/2010

So, my question....

Why does the text to column conversion work when done manually but won't work as a macro?

Answer 1

Bonsour®

"Fazi67" a écrit

M trying to convert a date column (eg 31/12/2010 format) into Dec-2010 format.

I use the text to column function in excel 2007.

Whilst recording the macro, it converts everything perfectly, including the

date requirement.

However, when I run the macro on a fresh set of text, it converts the text to

columns, but some - not all - of the date fields swap the day and month numbers

around (i.e. as in the US date display), whilst the remaining dates revert back

to text.

An extra catch is that the sheet reads the cells in the european date

configuration, so the day becomes the month and the month becomes the day.

e.g. 31/12/2010 - displayed as 12/2010  when working properly, becomes

31/2010<http://www.excelforum.com/images/smilies/confused.gif>

So, my question....

Why does the text to column conversion work when done manually but won't work

as a macro?

it's a little tricky ;o)))

when recording "you" are using worksheet interface ...

though" you" use local format for dates (Excel Options properties)

but ...

macro(VBA)  "always" use US langage i.e. US format for dates !!!

so when recording the macro (step 3 in convert wizard)

you have to precise  for the date fields  the format you read

i.e.  DMY versus MDY

HTH