How to change the locale of a formatted date?

Question

I want to retrieve today's date in a specific format with English month name.

I'm using Format(DateValue(Now), "dd-mmm-yyyy"), which gives me 05-cze-2013, which is in Polish. What I want to get is 05-Jan-2013.

I am only interested in a VBA solution. Please also provide a way to set the locale back to the original, also using VBA.

Answer 1

It's not clean VBA but it's a way to get localized formatted date in string. Something that Format function can't do.

With Range("$A$1")
    .NumberFormat = "[$-809]dd mmmm"
    .FormulaR1C1 = "=NOW()"
    tmp = .Text
    .NumberFormat = "@"
    .Value = CStr(tmp)
End With

More info about localized formatting i great answer https://stackoverflow.com/a/899290 from Ian Boyd

Answer 2

It's not very difficult...

Sub VBA_Dates_Format()
    Cells.Clear
    Range("A1").Value = Now
    Range("A2").Value = Now
    ' Polish
    Range("A1").NumberFormat = "[$-415]d mmm yy;@"
    ' English UK
    Range("A2").NumberFormat = "[$-809]d mmm yy;@"
End Sub

I have achieved this by recording and modifying a macro to fit the criteria.

---

Further reading available here