VBA: Format Date to US Standard

Question

I have a Macro that writes an email daily. For the email title, the macro formats the date input found in a specific cell, like this:

Period = ThisWorkbook.Sheets("aux").Cells(2, 1)
MsgBCC = ""
MsgFrom = "----Specific email address here----"
MsgSub = "Daily Update  (" & Format(Period, "dd/mmm") & ")"
Msg = "<html><body><font color=#303030 size=""3"" face=""Trebuchet MS"">"

The thing is: I want to change the formatting of the date so it displays the month name in English (which is not the default language of my computer). I want the date to be of the "dd/mm" format, like "10/Feb" for February 10th, for instance.

I can change the date formatting to English by using the "Custom" format in a worksheet. The "coordinates" of the format I desire are [$-409]mmm/aa;@. They are Excel generated, like this:

When I try to use this format in the VBA Format function, however, the date output does not change.

Any ideas of how I could alter the date format?

Application.Text(date(),"[$-40e]dddd") stolen from sites.google.com/site/e90e50fx/home/… — Doug Coats
I think also you just needed Period.Value or Period.Text, as you were trying to format a Range object, when what you actually wanted was its value! So Format(Period.Value, "dd/mm") — Wolfie
@DougCoats You should post that as an answer, not a comment. — TylerH

Doug Coats Doug Coats · Accepted Answer · 2017-02-10T14:53:31

Application.Text(date(),"[$-40e]dddd")

Reference:
https://sites.google.com/site/e90e50fx/home/Month-day-names-with-Excel-formula

VBA: Format Date to US Standard

1 Answers