how format date with TEXT() function for international usage?

[AlexBY]

=TEXT(DATE(2009,4,1),"mmmm") doesn't work for different locales. This one is
done for english based locale, but will not work for russian because format
is different - "мммм". So I can make a document, but nobody with different
locale can read it!!!

How to specify format internationally and people with different locales can
read document and see how functions work?

 

[AlexBY]

Dear Hector,

my task is simple: I need to generate a report every .... I try to automate
the process and help myself with functions.
1. I get the name of month from the name of file and feed all necessary
texts in excel document with it.
2. Ready document I send to particular recipients in different countries
with different locales.

There is no any shared workspace for files location.
"get.workspace(37)" doesn't work in my MS Excel 2003, may be I need to setup
something else, but my recipients will do the same - that's bad idea.

All I can to do now - replace formulas with values (Copy and then paste
values) - but it's not a good idea as well.

 

[Harlan Grove]

=TEXT(DATE(2009,4,1),"mmmm") doesn't work for different locales. This one is
done for english based locale, but will not work for russian because format
is different - "мммм". So I can make a document, but nobody with different
locale can read it!!!

How to specify format internationally and people with different locales can
read document and see how functions work?

Simplest to avoid the problem and use dates in ISO format, e.g.,
DEFINE the NAME _DATE_ referring to the [english] formula

=TEXT(YEAR(date_here)*10000+MONTH(date_here)*100+DAY
(date_here),"0000\-00\-00")

then use =_DATA_ wherever you want the date to appear.

If you absolutely must use full month names, then try Hector Miguel's
suggestion again, but this time follow his instructions EXACTLY! You
neet to define the name MonthText, i.e., run the menu command Insert >
Name > Define, enter MonthText as the name and the formula

=REPT(INDEX(GET.WORKSPACE(37),20),4)

as its definition. You can't use the GET.WORKSPACE function directly
in cell formulas, only in defined names or in XLM macro sheets. Once
you've defined this name you *CAN* use it in cell formulas like

=TEXT(DATE(2009,4,1),MonthText)

This *DOES* work in Excel 2003.