• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel Dates Displayed in Different Languages

You are here: Home / Excel Formulas / Excel Dates Displayed in Different Languages
June 1, 2017 by Mynda Treacy

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:

Excel Dates Displayed in Different Languages

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:

language ID

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:

for use with the text function

Download the workbook and follow along

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:

using data validation to automate translations

The key to this is the TEXT formula. Letโ€™s look closer at the formula in cell L6:

automating date translations

The VLOOKUP part of the formula references โ€˜Table1โ€™ (image below) using Structured References, as opposed to cell references:

using structured 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:

automated display of different languages

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:

automating date translations with slicers

Our TEXT Formula then references the PivotTable (cell I8), which displays the language selected in the Slicer:

reference the Pivottable

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:

chart title changes to match selected language

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.

Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Formulas Posts

Summarize Months to Quarters

Excel Formulas to Summarise Monthly Data into Quarters

3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions

Excel BYCOL and BYROW Functions

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively

How to Use Python in Excel Natively

How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling

Excel Functions for Financial Modeling

Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you donโ€™t have to waste time learning and why.
excel advanced formula environment

Excel Labs (Formerly, Advanced Formula Environment)

Excel Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.


Category: Excel Formulas
Previous Post:Power Query Updates
Next Post:Script Lab โ€“ Explore the Office JavaScript API & Learn to Build Add-InsScript Lab Logo

Reader Interactions

Comments

  1. Hans

    April 2, 2021 at 2:03 am

    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

    Reply
    • Mynda Treacy

      April 2, 2021 at 9:16 am

      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

      Reply
  2. Thierry

    March 19, 2021 at 9:15 pm

    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

    Reply
    • Mynda Treacy

      March 21, 2021 at 11:56 am

      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

      Reply
      • Thierry

        March 29, 2021 at 7:56 pm

        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

        Reply
        • Mynda Treacy

          March 31, 2021 at 4:48 pm

          Thanks for clarifying, Thierry. Unfortunately, this technique doesn’t apply to number values, sorry.

          Reply
          • Thierry

            April 13, 2021 at 4:58 pm

            So unlucky…. thanks a lot for your answer, Mynda.

  3. Deirdre

    February 18, 2021 at 2:00 am

    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

    Reply
    • Mynda Treacy

      February 18, 2021 at 8:46 am

      Hi Deirdre,

      You could try wrapping the TEXT formula in LEFT e.g.

      =LEFT(TEXT($L$4,"[$-0052]mmm"),3)

      Mynda

      Reply
      • Deirdre Leigh

        February 18, 2021 at 7:33 pm

        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

        Reply
        • Mynda Treacy

          February 18, 2021 at 7:35 pm

          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

          Reply
          • Deirdre Leigh

            February 19, 2021 at 7:45 pm

            Thanks Mynda

  4. Jozef

    February 14, 2021 at 8:33 pm

    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”

    Reply
  5. Damien GUSTIN

    November 29, 2020 at 4:34 am

    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 !

    Reply
    • Mynda Treacy

      November 29, 2020 at 10:31 am

      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

      Reply
  6. Corand

    November 28, 2020 at 6:51 am

    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

    Reply
    • Mynda Treacy

      November 28, 2020 at 3:01 pm

      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

      Reply
  7. Bert van der Meer

    June 25, 2020 at 12:26 am

    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!

    Reply
    • Mynda Treacy

      June 25, 2020 at 2:36 pm

      So pleased to hear that, Bert ๐Ÿ™‚

      Reply
  8. Laila

    July 9, 2019 at 7:30 pm

    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?

    Reply
    • Catalin Bombea

      July 10, 2019 at 1:41 pm

      Hi Laila,
      There is a link provide in this article, with all languages. Here is the link again.

      Reply
      • Laila

        July 10, 2019 at 8:14 pm

        Hi Catalin,

        Thank You so much, very good information, just what I needed

        Reply
  9. William

    January 7, 2019 at 11:10 pm

    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’.

    Reply
    • Catalin Bombea

      January 8, 2019 at 2:51 am

      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″)

      Reply
  10. Apra

    June 27, 2018 at 10:28 am

    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.

    Reply
    • Catalin Bombea

      June 27, 2018 at 11:44 pm

      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

      Reply
  11. Dodo

    September 30, 2017 at 11:51 pm

    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

    Reply
    • Mynda Treacy

      October 1, 2017 at 9:39 am

      Hi Dodo,

      Thanks for spotting the Hebrew/Arabic error. I’ve fixed it now.

      Cheers,

      Mynda

      Reply
  12. KingTamo

    August 5, 2017 at 5:35 pm

    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?

    Reply
    • Catalin Bombea

      August 6, 2017 at 12:07 am

      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

      Reply
      • KingTamo

        August 6, 2017 at 10:31 pm

        Thank you very much Mr. Catalin for this useful information.
        It worked well now..

        Reply
  13. Kimmo

    June 22, 2017 at 2:42 am

    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.

    Reply
    • Mynda Treacy

      June 22, 2017 at 1:42 pm

      That’s right, Kimmo, the format argument is not converted automatically by Excel if you open the file with a different language setting.

      Reply
    • Vladi

      July 14, 2023 at 7:47 pm

      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?

      Reply
      • Mynda Treacy

        July 16, 2023 at 3:10 pm

        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

        Reply
  14. jim

    June 1, 2017 at 10:19 pm

    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) ๐Ÿ™

    Reply
    • Mynda Treacy

      June 2, 2017 at 9:30 am

      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

      Reply
      • jim

        June 2, 2017 at 6:45 pm

        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!)

        Reply
        • Mynda Treacy

          June 2, 2017 at 9:09 pm

          Glad you enjoyed it, Jim ๐Ÿ™‚

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.