MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Naming a sheet by a date


Posted by rob on January 23, 2002 2:18 AM

I want to name a sheet using the Date function i.e.

Sheets(lastSheetName + 1).Name = Date

But the default for date in my regional setting is dd/mm/yyyy so the / character won't go into the sheet name. Is there a way I could convert the date into a string so the it came up as dd_mm_yyyy? I know I can just change the regional setting but this needs to be used on more then one computer. Thanks for help in advance.
Rob


Posted by Mudface on January 23, 2002 2:32 AM

Use the following-
dim myDate
myDate = Format(Date, "dd mm yy") ' or dd_mm_yy
Sheets(lastSheetName + 1).Name = myDate

Posted by Mudface on January 23, 2002 2:33 AM

Use the following-
dim myDate
myDate = Format(Date, "dd mm yy") ' or dd_mm_yy
Sheets(lastSheetName + 1).Name = myDate

Posted by Robb on January 23, 2002 2:38 AM

Rob

Try using:

Sheets(lastSheetName + 1).Name = Format(Date, "dd_mm_yyyy")

Any help?

Regards

Robb

Posted by Staffan on January 23, 2002 2:45 AM

If I understand you correctly this code snippet should help ...

Public Function testFormatDate(ByVal myDate As Date)
Dim myString As String
'Here comes what I think you actually are looking for ...
myString = Strings.Format(myDate, "dd_mm_yyyy")
' My String now contains what I think you want
testFormatDate = myString
End Function