TEXT(Cell1,"MMMM YYYY") to work in all locale.

[Guest]

I originally wrote in a previous email, (between the stars), and a reply mentioned to try this

hat you can do:

you have a date in A1, in A2 you write your desired textformat eg. MM JJJJ
(just type in the text). In A3 use the formula
=text(a1, a2) which will give you the correct result.

but this does not work,
in cell A1 I have 01.01.2004 (German date version)
in cell A2 I have =A1 but with the cell format as MMMM YYYY showing Januar 2004
in cell A3 I have ="My Date: "&TEXT(A1;A2) showing My Date: 37987

what I'm after is My Date: Januar 2004 and when the file is opened an machine were the locale is English it would read My Date: January 2004

I've looked at using ="My Date:" & TEXT(A2;CELL("format";A2)) but this only returns D3 and I look in the help on format and it mentions that D3 represents mmm-yy .... not much help either.

any ideas much appriciated

*********************
The following funtion works on a computer that has "English" date format setting each YYYY to represent a four digit year, but if view the same workbook on a computer that has "German" settings I don't see a four digit year I only see YYYY. The letter that represents Y for Year in German is J for Jahr, so I know it relates to the locale. My question is that if Excel is locale supportive in that the IF function is translated WENN or VLOOKUP is translated to SVERWIES then how come it does not translate text formats like MMMM YYYY and translate them automatically to MMMM JJJJ. In any case how do I overcome this problem.

(English settings)
="Month :- " & TEXT($A$2,"MMMM YYYY") where A2 = 01/01/2004 I would see Month :- " January 2004"

(German settings)
="Month :- " & TEXT($A$2,"MMMM YYYY") where A2 = 01.01.2004 I would see Month :- " Januar YYYY"
**************

 

[arno]

Hi Kevin,

you have a date in A1, in A2 you write your desired textformat eg. MM JJJJ
(just type in the text). In A3 use the formula
=text(a1, a2) which will give you the correct result.

but this does not work,

yes, it does in a german version, (write in A2 MM YYYY to test in english).

in cell A1 I have 01.01.2004 (German date version)
in cell A2 I have =A1 but with the cell format as MMMM YYYY showing Januar

2004
do not do this, type MMMM YYYY and nothing else in the cell. i did not tell
to write =A1 and apply any format

in cell A3 I have ="My Date: "&TEXT(A1;A2) showing My Date: 37987

try again

arno

 

[Guest]

Thanks very much, I'll read more carefully next time