Is there a way to force a locale when formatting a date in Outlook?

Question

I am wondering if there is an Outlook VBA equivalent to "[$-409]mmmm yyyy" that lets Excel display a date in the locale you want.
Format(Date, "[$-409]mmmm yyyy") is just silently ignored and only displays in the system locale (I need to use 2 different locales at two different places so changing the system locale is not an option).

EDIT: My question is actually pretty much a duplicate of this one and it seems not possible in either application Outlook or PowerPoint.

My understanding is the $-409 means display a date in the American version of "mmmm yyyy". That is, display the month as January, February and so on rather in the language defined by the system locale. What two locales do you have? — Tony Dallimore, Jun 05 '17 at 14:54
There is some relevant information in this answer https://stackoverflow.com/a/899290/973283. — Tony Dallimore, Jun 05 '17 at 14:58
@TonyDallimore you're right about the meaning of `$-409`, but it only seems to work in the cell number format of Excel and not with VBA, which is the purpose of my question here. — YeO, Jun 07 '17 at 11:08

score 0 · Answer 1 · answered Jun 07 '17 at 14:38

Sorry I did not make my point clearly in my comment.

When you include “$-409” in a format, it is you that has decided that an American format date is required. Excel may help you create an American format date but it will not help you decide which format date is required.

It is you who will have to determine that Jesse gets an American date, John gets a British date and Jacque gets a French date. Once you have made that decision, VB will accept a country code when formatting a date but I do not believe that VBA offers any such functionality. Since you only have two locales, I suggest you do the conversion yourself.

Option Explicit
Sub Demo()

 Dim Prompt As String
 Dim Recipient As String
 Dim MonthsFrench() As Variant
 Dim Today As Date

 Today = Now()
 MonthsFrench = VBA.Array("", "Janvier", "Févier", "Mars", "Avril", "Mai", "Juin", _
                          "Juillet", "Auguste", "Septembre", "Octobre", "Novembre", "Décembre")
 ' I have used VBA.Array so lower bound is zero regardless of option base setting

 Debug.Print Format(Today, "d mmmm yyyy")
 Debug.Print Format(Today, "mmmm d, yyyy")
 Debug.Print Day(Today) & " " & MonthsFrench(Month(Today)) & " " & Year(Today)

 Do While True
   If Recipient = "" Then
     Prompt = "Recipient name?"
   Else
     Prompt = "Last recipient, " & Recipient & " preferred date is "
     Select Case Recipient
       Case "Jesse"
         Prompt = Prompt & Format(Today, "mmmm d, yyyy")
       Case "John"
         Prompt = Prompt & Format(Today, "d mmmm yyyy")
       Case "Jacque"
         Prompt = Prompt & Day(Today) & " " & MonthsFrench(Month(Today)) & " " & Year(Today)
       Case Else
         Prompt = "Last recipient not known"
     End Select
     Prompt = Prompt & vbLf & "Next recipient name?"
   End If
   Recipient = InputBox(Prompt)
   If Recipient = "" Then
     Exit Sub
   End If
 Loop

End Sub

Ok, I see your point, but mine was really to know if I could write the month name in English or French (or whatever else) with a locale setting rather than using the array workaround which is what I am using right now. It would have been nice to be able to use `Format(Date, "[$-409]mmmm yyyy")` for English and `Format(Date, "[$-40C]mmmm yyyy")` for French which Excel allows in the cell's formatting - but not in VBA — YeO, Jun 08 '17 at 11:20
@YeO With VB, you can write `date1.ToString("d MMMM", CultureInfo.CreateSpecificCulture("xx-yy")` where "xx-yy" can be "en-US" or "es-MX" or similar. You can also reference locale specific "short dates" and "long dates". I know of no equivalent with VBA. If you have access to a version of VB that allows creation of AddIns, you could create a suitable routine. I use the free Community version which does not have the necessary functionality. — Tony Dallimore, Jun 08 '17 at 12:16
I use Application.WorksheetFunction.Text(Date, "[$-th-TH,107]d mmm yyyy;@") — Boontawee Home, Jun 25 '22 at 18:27

Is there a way to force a locale when formatting a date in Outlook?

1 Answers1