If you share Excel files and reports with users from different countries then you might want to give them a report in their language…and I don’t just mean converting from English to American 😉
It’d be a load of work to create a separate file for each language, so instead we can automate it like this:
Note: if your users have their PC language set to their locale then you don't need to use this technique, because Excel will automatically convert date formats to their local language. This is for users who want to view their reports in a language that's different to their PC language settings.
Excel Dates Displayed in Different Languages
We use the TEXT Function to convert the dates by specifying the language ID in the format argument of the formula. For example:
=TEXT("1/1/2017","[$-0809]dddd")
=Sunday
Where [$-0809] is the language ID for English, and dddd tells Excel to covert the date to the full name of the day.
List of Language ID’s
There is an extensive list of language ID’s available from Microsoft here. Below is a snippet:
In Excel 2016 we can either use the last four digits of the Language ID or the Language tag. For other versions of Excel it’s safer to use the last four digits of the Language ID.
For use with the TEXT Function we need to wrap the language tag or ID in [$- xxxx], like so:
Download the workbook and follow along
Enter your email address below to download the sample workbook.
Automating Date Translations
Now that our file contains a table with the different languages we want to support, we can use a Data Validation list to automate the translation:
The key to this is the TEXT formula. Let’s look closer at the formula in cell L6:
The VLOOKUP part of the formula references ‘Table1’ (image below) using Structured References, as opposed to cell references:
Let’s translate the formula:
=TEXT($L$4,VLOOKUP($I$4,Table1[[Language]:[Language ID]],3,0)&"dddd")
In English it reads:
Convert the date in cell L4 to text by looking up the language selected in the data validation list in cell I4, in the Language column of Table1 and returning the language ID from the 3rd column. Join the language ID to ‘dddd’ so that Excel knows the language and date format to return.
Tip: in the file I’ve included in this post you’ll notice that cells L7, L8 and L9 contain similar TEXT formulas with different date formats (ddd, mmmm and mmm).
Note: if your PC language setting isn't English then you may need to change the ddd/dddd/mmm/mmmm formats to that of your locale.
Automating Date Translations with Slicers
Another option is to use a Slicer to allow the user to select the language they want to see, like this:
Taking a closer look at the mechanics of this example you’ll see we have a PivotTable in cells I7:I8, which is purely for the Slicer. You can see in the image below that it only contains a single field in the row labels area:
Our TEXT Formula then references the PivotTable (cell I8), which displays the language selected in the Slicer:
This translates the ‘Day’ in the first table and the ‘Month’ in the second table, which feed the charts.
Translate Chart Title
The Chart Title in cell I5 also changes to match the selected language, but if you look the formula you’ll see that it’s simply a VLOOKUP formula that returns the matching title from Table2:
Note: Excel can convert dates into other languages, but it can’t convert words.
Thanks
Thanks to Drazen from ExcelalaCarte.com, who shared this Excel Dates Displayed in Different Languages idea with me, which I extended to include the Slicer technique.
Power Query Updates
Hans
Hello Mynda,
I am searching for common documentation about all available codes or id’s between [ ] that can be used for cell formatting.
in your blog you give a list of language id’s but for e.g. a time format you can use [u]:mm:ss.
Thanks in advance for you reply
Mynda Treacy
Hi Hans,
I think you mean [h]:mm:ss and this isn’t a language setting, it simply tells Excel to sum the hours rather than show them as a time. I’m not aware of any language settings for anything other than date formats.
Mynda
Thierry
Hello,
First thank you for this good explanation ot the “[$-xxx]” trick….
Then a question : do you have an idea (or solution) how to get rid of the Regional Settings set on the computer that launch the XLS file ?
I give you an example to clarify :
If you use =TEXT(“1/1/2017″,”[$-0809]dddd”) it works like a charm if the Regional Settings (in Windows) are configured for english, but if someone has the Regional Settings set to an other language he should use others “day code” like =TEXT(“1/1/2017″,”[$-0809]jjjj”) for french, because days are “jours” in french.
Thanks in advance
Best regards
Mynda Treacy
Hi Thierry,
If your users have their PC language set to their locale then you don’t need to use this technique, because Excel will automatically convert date formats to their local language. This is for users who want to view their reports in a language that’s different to their PC language settings.
Mynda
Thierry
Hello,
Thank you for your answer. I understand that, but imagine a user wants to see the number as they are formated in an other coutry. For example, in US or GB you will write one million like this : 1,000,000.00 and in FR like this: 1’000’000,00
If the user also wants to see the numbers as they are displayed in an other settings.
Is there a trick to also change the display of the numbers ?
Thanks a lot
Best regards
Mynda Treacy
Thanks for clarifying, Thierry. Unfortunately, this technique doesn’t apply to number values, sorry.
Thierry
So unlucky…. thanks a lot for your answer, Mynda.
Deirdre
I’m using the language tag to produce charts in English and Welsh (separate), but ideally I need the months abbreviated to only the first three characters as in Jan / Feb etc, so Ion / Chw in Welsh. But Feb comes through as Chwef which takes up a lot of extra room.Reducing the mmm to mm obviously changes the months to numbers so is there any way I can change this (other than reverting to numerical dates?) Thanks. Deirdre
Mynda Treacy
Hi Deirdre,
You could try wrapping the TEXT formula in LEFT e.g.
Mynda
Deirdre Leigh
Hi Mynda, thanks so much. I was hoping to add it to the chart axis number format box – currently have [$-cy-GB,1]d mmm ‘yy which works fine for Jan (Ion), Mar (Maw), Apr (Ebr), May (Mai), Jun (Meh) and Oct (Hyd), but annoyingly not for Feb (which comes out as Chwef), Jul (Gorff), Aug (Awst), Sep (Medi), Nov (Tach) and Dec (Rhag). The longer ones, esp Chwef after Ion, spoils the layout. If you can help further that would be great. Deirdre
Mynda Treacy
Hi Deidre,
You can’t force the custom number format to truncate the last character for February. Better to put the format in the source data for the chart.
Mynda
Deirdre Leigh
Thanks Mynda
Jozef
Hi Damien,
just a hint. Maybe try to use Left function.
Example: =Left(Text(date(2021;1;10);”[$-0809]ddd”);2)
Result will be “Su”
Damien GUSTIN
I tried to use the formula as explained : TEXT(C4;”[$-0409]dd”) to obtain the 2 first letters of the day name in English (eg. “Mo” for “Monday” if the date in C4 is a Monday.
But with 2 “dd”, it doesn’t work and gives the date number.
With more than 2, (3 or 4), it works !
Mynda Treacy
Hi Damien,
“dd” in a custom number format returns a numeric representation of the day. “ddd” returns the first 3 letters of the day and “dddd” returns the whole day name, hence why your formula only works with 3 or 4. More on custom number formats here: https://www.myonlinetraininghub.com/excel-custom-number-format-guide
Mynda
Corand
Dear Mynda Treacy, thank you very much for the solution you suggested for the topic “Excel Dates Displayed in Different Languages”, I really appreciated your description.
I implemented it using Windows 10, but unfortunately the solution does not work with Office 2016 for MAC and macOS Big Sur 11.0.1 … … …
Could you suggest some other workaround?
Regards, Corand
Mynda Treacy
Hi Conrad,
Looks like those language IDs only apply to Windows OS. I’m not sure if you can alter the language settings inside a formula on a Mac. To check you can open the format cells dialog box and go to Date. There should be a drop down list for the Locale (location). Choose a date format that has the month name in it, or at least abbreviated to 3 characters. Apply that setting, then open the Format Cells dialog box again, then go to Custom. The current format should be selected and it should show the language ID inside square brackets. This will at least tell you if Mac OS has language codes. Then you’ll just need to find the ones you want to use by repeating the above steps for each language.
Hope that helps. If you get stuck please post your question on our Excel forum where you can also upload screenshots and we can help you further.
Mynda
Bert van der Meer
I was stressing out printing annual accounts. The first print included the right number format (. for thousand, , for decimals), the second one (after changing the regional settings) the right date format, but the numbers were wrong.
Thanks to you I managed to get all of these in one document.
This is so great. You made my day. Thank you Mynda!
Mynda Treacy
So pleased to hear that, Bert 🙂
Laila
Hello this is absolutely great but I miss arabic this is very important for me is there any way to insert that in the list? Can anyone help me please?
Catalin Bombea
Hi Laila,
There is a link provide in this article, with all languages. Here is the link again.
Laila
Hi Catalin,
Thank You so much, very good information, just what I needed
William
Is there a way to make the same trick work for number formats.
I need to construct a TEXT call that always expresses numbers in Euro format (#,00).
If I use ‘TEXT(123.4,”#,00)’, I get the integer part without the separator, ie: ‘123’.
Catalin Bombea
Hi William,
If the dot is the decimal separator, then it looks like you are using in your format the thousands separator, basically your format excludes decimals.
If you want to see decimals, use the same decimal separator from your regional settings:
TEXT(123.4,”#.00″)
Apra
Can we convert any text using the above function, not just date.I am looking for some formulas which can help me in converting entire spreadsheet in foreign language to English.Can some one has any clue.
Catalin Bombea
Unfortunately no, Excel is not a translation tool.
It’s better to create a duplicate sheet for each language, or set formulas in text cells to get the proper text in the selected language from a hidden sheet.
Cheers,
Catalin
Dodo
Thanks for the article and the examples. It helped.
One mistake: In the “Automating Date Translations” example, when Hebrew language is chosen, the text appear in Arabic.
Probably the wrong language code
Dodo
Mynda Treacy
Hi Dodo,
Thanks for spotting the Hebrew/Arabic error. I’ve fixed it now.
Cheers,
Mynda
KingTamo
Thanks a lot for this awesome tutorial
I have added a row for another language (Arabic) in “Slicer Example” sheet in A21:G21 then right-click to Refresh Slicers ..but it doesn’t refresh the new language added .. Any idea why does this happen?
Catalin Bombea
Hi KingTamo,
The data for that Pivot table comes from Data Validation Example sheet, not from Slicer example sheet. Add that row for your language o that sheet, and refesh the pivot table, it will work.
You can find the source of a pivot table if you select the pivot table, then from Pivot Table Tools tab in ribbon, from Analyze tab, click on Change Data Source, this will take you to the data source, you don’t have to actually change the source.
Catalin
KingTamo
Thank you very much Mr. Catalin for this useful information.
It worked well now..
Kimmo
Hi.
As you said in note “if your PC language setting isn’t English then you may need to change the ddd/dddd/mmm/mmmm formats to that of your locale.”.
This means that e.g. in Finnish, you need to use “t:mm pp.kk.vvvv” instead of “h:mm DD.MM.YYYY”.
Afaiu these values are in quotes, so they are not automatically translated, like function names, e.g. today() == tama.paiva() when i open excel sheet in Finnish language installation.
Mynda Treacy
That’s right, Kimmo, the format argument is not converted automatically by Excel if you open the file with a different language setting.
Vladi
Hi Kimmo,
Did you find the final solution for this problem DD.MM.YYYY => pp.kk.vvvv?
The only what I found is, there is an universal variable for the Years “eeee” and it is works. Maybe somebody knows universal replacers for Days and Month too?
Maybe Mynda Treacy knows universal replacers for Day, Month and Year???
General question to Microsoft, why they decide to translate formulas and even more, why they did translate syntax as well? the separator in formula in US is comma “,”, but in Europa it is comma-dot “;”.
Why they do like that? To make life harder?
Mynda Treacy
Hi Vladi,
If you use any of the formats prefixed by an asterisk, they will automatically translate according to the locale of the PC the file is opened on. Similarly, any formats prefixed with x-systime or x-sysdate will automatically update based on your PC locale.
Of course, with a custom number format, you cannot simply prefix it with an asterisk or x-systime, so if you’re wanting to use the technique described above and open it on PCs with different locales, I’m not sure there’s a solution, sorry.
Mynda
jim
Great trick!
would you believe I’ve just started reporting for some of our Welsh plants, so I can change the date language if one of those is selected!
I’ve used conditional formatting in the table, which leaves them as numbers. However, for charting, looks like I have to use the TEXT route, which messed up the sorting (using pivots – I think I’ll have to add another column to sort on) 🙁
Mynda Treacy
Hi Jim,
Yes, PivotTables and PivotCharts will be tricky. I’d check if your Welsh colleagues have their PC language settings set to Welsh, because Excel automatically translates date formats based on your PC language settings. Of course if their language is set to English then they’d need to use this technique. And in that case you can either use regular charts, or use Power Pivot ‘Sort by’ to force the dates to sort on another column so they’re always in the correct order.
Mynda
jim
and we can’t use the =–cellref trick to change it back into a date – double 🙁
(but still SO good – I love these little quirks; so much better than really useful stuff!)
Mynda Treacy
Glad you enjoyed it, Jim 🙂