Formula / Format to convert English date to another language

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm creating a multi-lingual Financial Statements spreadsheet.

I have monthly column headers in row 13 of one of the tabs (01-Jan-19, 01-Feb-19, 01-Mar-19 etc.) in a "mmm-yy" format. My Excel language setting is English. I need to keep these dates in English.
In the row directly underneath these dates, I would like a formula that converts these exact dates into another language. So for example, if I wanted to convert to French, the row below "Feb-19" would show as "Fev-19" as February in French is spelt Fevrier.

Is there a way to do this through 'Format cells (CTRL + 1) > Number > Custom'?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If you want also weekdays:
[$-fr-FR]dddd, mmmm d, yyyy

without weekdays:
[$-fr-FR]mmmm d, yyyy
 

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
If you want also weekdays:
[$-fr-FR]dddd, mmmm d, yyyy

without weekdays:
[$-fr-FR]mmmm d, yyyy

First things first, thank you for providing the solution.

I have the following further comments before I close this thread as solved:
  1. The above format leads to the month to begin with a lower case letter. Is it possible to format it such that the first letter of the month begins with a capital letter?
  2. Is there a list/source which shows the equivalent entries for other languages e.g. Spanish, German etc.
  3. Finally, if there is such a list/source (presumably there is), would it be possible for me to list them all in an array in Excel and then, depending on the language selection chosen by the user on the spreadsheet, the cells are conditionally formatted to the language specific format, perhaps by using INDEX MATCH?

    This might not make such sense so by way of example, the first tab on my spreadsheet has a DV list offering the user a choice of languages (English, French, German, Spanish etc.). I want the formatting of the dates to be conditional on this selection, and this condition would (presumably) work through the INDEX MATCH function i.e. IF user selects French as language setting on tab 1, INDEX the list/source in Excel and match the language selection with the format for the French language.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top