convert MM/DD/YYYY to DD/MM/YYYY while the data format is text

[Vogelmann]

can the date be changed from MM/DD/YYYY to DD/MM/YYYY while the format of data is `text`instead of `date`? I still need the data in `text`format, but I have to use a macro to change the order of days and months. Previously I changed the format from `date`to `text`by using the following code:

Please Login or Register  to view this content.

and I think it would be easier to manipulate this code to obtain the result that is desired.
Thanks in advance for your help,

Karol

[Bishonen]

Please Login or Register  to view this content.

will this do?

[Vogelmann]

nope, it doesn`t. And it can be applicable to only one cell.

When I used that code 27/03/2008 became 7//3//008

[Bishonen]

nope, it doesn`t. And it can be applicable to only one cell.

When I used that code 27/03/2008 became 7//3//008

Dunno, seems to be working for me.

[Vogelmann]

cut zeros from the beginning, e.g. 5/23/1975, 4/10/1988, that`s how my dataset looks like. I tried it in your file and after deleting zeros it doesn`t work.

[Bishonen]

Sorry, there was no mention of no "0's".

Please Login or Register  to view this content.

[Vogelmann]

after editing, your code produces the following error:

`Invalid procedure call or argument`

and then the following row is highlighted:

Please Login or Register  to view this content.

besides this, it still cuts numbers, e.g. 2/4//200 instead of 4/25/2008.

[jindon]

Try this

Please Login or Register  to view this content.

[Bishonen]

the msgbox' were for debuging. I thought that the last one worked ^^ This one does for sure though:

Please Login or Register  to view this content.

[Vogelmann]

jindon - your code works, though it changes the format to `custom` instead of `text`

Bishonen - what if my range is G7:G ? I cannot apply your code to that range, could you help me?

[jindon]

jindon - your code works, though it changes the format to `custom` instead of `text`

Please Login or Register  to view this content.