Month and day names in all languages with Excel formula

posted May 9, 2013, 3:19 PM by Krisztina Szabó   [ updated May 9, 2013, 3:45 PM by r ]
[...] Dr. Frankenstein: For the experiment to be a success, all of the body parts must be enlarged.
Inga: His veins, his feet, his hands, his organs vould all have to be increased in size.
Dr. Frankenstein: Precisely.
Inga: [her eyes get wide] He vould have an enormous schwanzschtücker.
Dr. Frankenstein: [ponders this a moment] That goes without saying.
Inga: Voof.
Igor: He's going to be very popular. [...]


Special thanks to Zoran Stanojević, a friend form Linkedin who shared this great trick with us.



Excel can show you the name of the month and day in many different languages via Date/Custom formatting and TEXT formula. By default both works according to the language setting of your Windows Regional settings. Important to note that Windows language setting could be different from the language of your Excel! There is a way to translate the month and day parts of the date to other languages. The magic key of the translation is the language code which could be used in the Date/Custom formatting code and also in the second argument of TEXT formula.

Format Cells

Go to Format Cells / Date and you can see a Locale (location) drop-down list below the type list. The default setting is the same as your Regional setting.


If you choose here a different language, Excel will generate a Custom formatting code using the language code of your choice.
So if we choose Italian together with the long date format, you can see the date with Italian month name.


If you are curious what is in the background, check the Custom formatting:


You can see that a special code [$-410] stands at the beginning of the usual format code. This is the italian language code or LCID assigned by Microsoft. You can find the full list of the codes here:

TEXT formula

In an earlier post we walked around the problems of language-dependent string parameters of TEXT formula. Just a short reminder: the letters used in the format codes (y for year, m for month, d for day, and also the time: h for hours, m for minutes, s for second) are dependent of the language setting of your Windows. So maybe you will need to use different letters in your formulas.

The codes could also be used in the second argument of the TEXT formula - for example as it is on the above picture:
=TEXT(A1,”[$-410]d mmmm yyyy”)
The code itself as listed on the above link is a hexadecimal number, you have to write it in square brackets with $- before. The leading 0 could be omitted.
So for example this formula:
=TEXT(A1,"[$-410]dddd")
will result the name of the day in Italian. In cell A1 I have TODAY(), so the result is martedi (=Tuesday).
And this formula will give the month name in Hungarian:
=TEXT(F4;"[$-40e]mmmm")
május (= May)

A friend of mine, Andrea Novelli from Italy created a summary file with all the language translations. I created an international version of it to avoid the problems of the language-dependent string parameters. You can download it from here.

TEXT formula via VBA

When you use formulas via VBA Application.Worksheetfunction you have to use the english string parameters, so in case of TEXT for example use d for day:
Application.Worksheetfunction.Text(date(),"[$-40e]dddd")
or:
Application.Text(date(),"[$-40e]dddd")

Both will result the Hungarian name of the day (40e is the code of the Hungarian language) under every language setting.
Ĉ
r,
May 9, 2013, 3:19 PM
Comments