Change the date separator

[shajueasow]

Hi,
I have a macro which uses a date entered in a cell to be used as the sheet name.
It works OK as long as the date separator is "-". But in some machines it is "/" and then the code fails as sheet names cannot have "/" in it.
How can this problem be solved other than manually changing the date separator from Control Panel>Regional and Language options>Date>Short Date>Date Separator (in Win XP)

TIA.

 

[xenou]

You can coerce the date to a value that is acceptable in sheet names:

Dim x
x = Format(Range("A1").Value,"yy-mm-dd")

Also possible (and useful) is a simple date expression in yyyymmdd format such as 20111231

 

[shajueasow]

That was indeed a clever move.
Thanks A Lot "xenou", for it as it does not require any "global" changes in the system.