Date formatting

  • Thread starter Thread starter lasca
  • Start date Start date
L

lasca

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
 
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
 
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)
 
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
 
Back
Top