TEXT function to format a date in English & French

[IanC]

I have cells formatted with TEXT and the format_text "mmmm yyyy".
Unfortunately this does not work in Europe as they expect (say in France)
"mmmm aaaa". Is there any way around this to use TEXT in more than 1
language?

 

[Frank Kabel]

Hi
the only solution I have for this:
- create an empty sheet which stores some language dependent information
(you can hide this)
- e.g. in row 2 enter the different date formats per language (with row 1 as
heading row for the language name)
- in X1 enter the current language name (maybe use a VBA function to
determine the current language setting automatically)

Now create a formula such as:
=TEXT(A1,HLOOKUP('language_sheet'!X1,'language_sheet'!A1:F20,2,0))

 

[IanC]

Absolutely brilliant. Thank you.

Hi
the only solution I have for this:
- create an empty sheet which stores some language dependent information
(you can hide this)
- e.g. in row 2 enter the different date formats per language (with row 1 as
heading row for the language name)
- in X1 enter the current language name (maybe use a VBA function to
determine the current language setting automatically)

Now create a formula such as:
=TEXT(A1,HLOOKUP('language_sheet'!X1,'language_sheet'!A1:F20,2,0))