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

[Guest]

Hi TWIM
(read previous threads to follow what going on
OK writting in A2 MMMM YYYY is OK for English but when a user opens the file in Germany or another country they just see in A3 My Date Januar YYYY

If the user changes MMMM YYYY in A2 to MMMM JJJJ it shows the correct date, but this requirement defeats the object if each user needs to change the value in A2 for A3 to show the correct date. So it appears that the value in A2 is also hard coded and is not changed by the locale settings of a users computer, so my search for a solution still continues

Thanks anywa
regard
K

----- arno wrote: ----

Hi Kevin

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

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 Janua

200
do not do this, type MMMM YYYY and nothing else in the cell. i did not tel
to write =A1 and apply any forma

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

try agai

arn

 

[arno]

Hi Kevin,

have one formula to set the language for you. in one cell you write your
formula in
C1 =text(today(), "MM JJJJ").
in C2 use the following formula to find out if excel is in english or
german:

=IF(right(c1, 4)="JJJJ", "GERMAN", "english")

Now, your format cell (A2) has the formula:
=if(c2="GERMAN", "MM JJJJ", "MM YYYY")

(You could have this in one formula, but in 2 it is easier to understand. If
you have more than 2 languages use a table with all possibilities and
VLookup.)

dadldo the job

arno

Hi TWIMC
(read previous threads to follow what going on)
OK writting in A2 MMMM YYYY is OK for English but when a user opens the

file in Germany or another country they just see in A3 My Date Januar YYYY.

If the user changes MMMM YYYY in A2 to MMMM JJJJ it shows the correct

date, but this requirement defeats the object if each user needs to change
the value in A2 for A3 to show the correct date. So it appears that the
value in A2 is also hard coded and is not changed by the locale settings of
a users computer, so my search for a solution still continues,