Format(SomeDate,"MM/dd") = "12-15" in VBA

Question

I'm writing a VBA macro in Excel that analyzes data from the spreadsheet and sends an email. In this macro, I have to attach the date formatted as "MM/dd" but the output is in the format of "MM-dd". So the question is, why is my slash getting replaced with a dash?

For simplicity, I have reduced the code to this example, and verified the problem exists with this example as well...

Private Sub Test()
    Dim Yesterday As Date: Yesterday = DateAdd("d", -1, Now)
    MsgBox Format(Yesterday, "MM/dd")
End Sub

When run, the message box shows "12-15" instead of "12/15" as expected.

Answer 1

I haven't used VBA myself, but I suspect it's treating / as "the current culture's date separator" - and you're presumably executing in a culture which uses - as the date separator. You could try quoting escaping the slash if you definitely always want a slash:

MsgBox Format(Yesterday, "MM\/dd")

That's a bit of a guess at both the cause and the fix, but it's at least worth a try.

EDIT: Thanks to GSerg for the correction of how to perform the right escaping in this context.

Answer 2

This has to do with your culture settings in Windows. Go to Control Panel - Regional and Language Options - Customize Regional Options - Date seperator. It's set to "-". If you prefer "/" you can change it there.

If you wish to format the date regardless of your default regional settings, you can do what Jon Skeet has said (escape the character).