View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default how format date with TEXT() function for international usage?

AlexBY wrote...
=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+D AY
(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.