Date value in VBA appears as mm/dd/yyyy instead of the actual format which is mm-dd-yyyy

[bellg]

Hi,

I can't get my my date to display in the format that I want it to as a string variable in vba.

Public Sub Todaydate()
    Today = Sheets("Date").Range("B1").Value
    MsgBox Today
End Sub

The msgbox displays 3/21/2017
The value in B1 shows as 3-21-2017

I want the variable "Today" to use the -'s so that I can use it to save in a filename. Does anyone know how to get VBA to display the -'s instead?

I even tried using some left, mid, right commands to pull the dates, but it doesn't seem to recognize the date format that the cell is saved into and always defaults to m/d/yyyy, so that doesn't work very well. I could add some if statements for months/days that are two digits, but I was curious if anybody knew why it defaults to /'s and how to avoid that.

Public Sub Todaydate()
    Today = Left(Sheets("Date").Range("B1").Value, 2) & "-" & Mid(Sheets("Date").Range("B1").Value, 4, 2) & "-" & Right(Sheets("Date").Range("B1").Value, 4)
    MsgBox Today
End Sub

 

[dmt32]

Hi,
If you just want to return what you see in the cell rather than it's underlying value then try using the Range.Text property & see if this solves your problem.

Today = Sheets("Date").Range("B1").Text

Dave

 

[Rick Rothstein]

Hi,

I can't get my my date to display in the format that I want it to as a string variable in vba.

Public Sub Todaydate()
    Today = Format$(Sheets("Date").Range("B1").Value, "m-d-yyyy")
    MsgBox Today
End Sub

What happens if you make the change to your code that I show in red above?

 

[bellg]

Yup, that solved it!!
Thanks Dave,
Greg