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'?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
If you want also weekdays:
[$-fr-FR]dddd, mmmm d, yyyy

without weekdays:
[$-fr-FR]mmmm d, yyyy
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top