Excel Dates Displayed in Different Languages

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

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 3^rd 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.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.