VBA to Convert Date to String?

[BAlGaInTl]

Hey all...

I want to prefix a filename in my macro with the date in the format.

yyyymmdd

To do this, I want to create a string (sDate) that will go on to be used with the GetSaveAsFilename function.

I've tried setting a string equal to the date function, but of course I get forward slashes in the string and that won't do. I also tried using the Year, Month, and Day functions applied to Date as well, but for example in January, I get 1 as the month and not 01.

What am I missing?

 

[daniels012]

If you want a formula to do it:
=TEXT(D5,"yyyymmdd")
will work.

If you want VBA , write back.

MIchael

 

[BAlGaInTl]

Yes, I'm looking for VBA...

 

[parmel]

You can use daniels012's construction in a macro as follows:

Sub mydate()
Dim sDate As String
sDate = Application.WorksheetFunction.Text(Cells(1, 1), "yyyymmdd")
MsgBox sDate
End Sub

This assumes the date is in A1

 

[parmel]

Oops, sorry daniels012. It was your solution - I shouldn't have jumped in.

 

[Scott Huish]

No need to call a worksheet function, assuming date in A1:

Sub mydate()
Dim sDate As String
sDate = Format(Range("A1"), "yyyymmdd")
MsgBox sDate
End Sub

 

[BAlGaInTl]

No need to call a worksheet function, assuming date in A1:

Sub mydate()
Dim sDate As String
sDate = Format(Range("A1"), "yyyymmdd")
MsgBox sDate
End Sub

Thanks...

I modified it slightly to this:

Sub mydate()
Dim sDate As String
sDate = Format(Date, "yyyymmdd")
MsgBox sDate
End Sub

It seems to be working.