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.

score 9 · Accepted Answer · 2013-06-05T15:50:55.740

9

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

edited Jun 05 '13 at 15:50

answered Jun 05 '13 at 08:42

**Mehow**, that's really impressive. I have not seen this trick done before. Do you have a link for further reading? I'd really like to understand how you accomplilshed this. – tbur Jun 05 '13 at 14:35
@tbur ive recorded and edited a macro, but also I have provided a useful link in case you wanted to dig deeper – Jun 05 '13 at 15:51
1

How can I do this with a String variable? `Dim myDate As String: myDate = Format(DateValue(Now), "[$-409]mmmm yyyy")` doesn't work, the month is still in english (my OS language)... – Kapé Sep 25 '15 at 23:50
I know this is old, but hopefully this helps more people understand locale codes. the `-409' represents the en-US language. Nothing, more and nothing less. using this defines what variables you need to use for linking year, day, month etc... For a more complete list on Locale Codes, and how to change actual language, and or number text then check out this answer here. https://stackoverflow.com/questions/54134729/what-does-the-130000-in-excel-locale-code-130000-mean/54540455#54540455 – IrwinAllen13 Feb 07 '19 at 21:54

score 2 · Answer 2 · answered Jun 25 '22 at 18:30

2

Try use

 Application.WorksheetFunction.Text(Date, "[$-th-TH,107]d mmm yyyy;@")

answered Jun 25 '22 at 18:30

Boontawee Home

935
7
15

score 0 · Answer 3 · edited May 23 '17 at 12:31

0

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

edited May 23 '17 at 12:31

Community

1
1

answered Dec 17 '15 at 11:07

b0rek

21
7

How to change the locale of a formatted date?

3 Answers3

Linked