Date formatting
 

I want to use a date for as an sheet name, Ie 13-Sep-08 or 13-09-08 ( this
will be set by using vb code).
My problem is the system date is in dd/mm/yyyy format and the "/" can not be
used for sheetname. How do i go about replacing the "/" with "-" in my vb
code so that it wil be an acceptable format.

Here is my code that I used


Dim SHEETNAME As String

SHEETNAME = Date

Sheets("IDT").Select
Sheets("IDT").Copy After:=Sheets(2)
Sheets("IDT (2)").Name = SHEETNAME

Date formatting
 

Hi,

Dim SHEETNAME As String

SHEETNAME = Format(Date, "dd-mmm-yyyy")
Sheets("IDT").Select
Sheets("IDT").Copy After:=Sheets(2)
Sheets("IDT (2)").Name = SHEETNAME



Mike

"lasca" wrote:

I want to use a date for as an sheet name, Ie 13-Sep-08 or 13-09-08 ( this
will be set by using vb code).
My problem is the system date is in dd/mm/yyyy format and the "/" can not be
used for sheetname. How do i go about replacing the "/" with "-" in my vb
code so that it wil be an acceptable format.

Here is my code that I used


Dim SHEETNAME As String

SHEETNAME = Date

Sheets("IDT").Select
Sheets("IDT").Copy After:=Sheets(2)
Sheets("IDT (2)").Name = SHEETNAME

Date formatting
 

Try something like

Sheets("IDT (2)").Name = Format(SHEETNAME,"mm-dd-yyyy")

Substitute the desired date format for "mm-dd-yyyy" in the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 13 Nov 2008 03:19:01 -0800, lasca
wrote:

I want to use a date for as an sheet name, Ie 13-Sep-08 or 13-09-08 ( this
will be set by using vb code).
My problem is the system date is in dd/mm/yyyy format and the "/" can not be
used for sheetname. How do i go about replacing the "/" with "-" in my vb
code so that it wil be an acceptable format.

Here is my code that I used


Dim SHEETNAME As String

SHEETNAME = Date

Sheets("IDT").Select
Sheets("IDT").Copy After:=Sheets(2)
Sheets("IDT (2)").Name = SHEETNAME

Date formatting
 

I don't have international programming experience, so I don't know if this
will always return the American date ordering (mm-dd-yyyy) or if it will
adjust to the local date preference of dd-mm-yyyy, but the Date function has
an alternate form that always outputs dashes instead of slashes... just add
a $ sign to the end of it.

Sheets("IDT (2)").Name = Date$

If this does always produce the American ordering, then use the Format
function as suggested by others.

By the way, if you do try this, could you post back whether the order is
local aware (did it put the month/day in the order you expected) or not so
that I will know for the future? Thanks

--
Rick (MVP - Excel)



"lasca" wrote in message
...
I want to use a date for as an sheet name, Ie 13-Sep-08 or 13-09-08 ( this
will be set by using vb code).
My problem is the system date is in dd/mm/yyyy format and the "/" can not
be
used for sheetname. How do i go about replacing the "/" with "-" in my vb
code so that it wil be an acceptable format.

Here is my code that I used


Dim SHEETNAME As String

SHEETNAME = Date

Sheets("IDT").Select
Sheets("IDT").Copy After:=Sheets(2)
Sheets("IDT (2)").Name = SHEETNAME