Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 8th 09, 07:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 2
Default how format date with TEXT() function for international usage?

=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?

  #2   Report Post  
Old May 8th 09, 07:37 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 434
Default how format date with TEXT() function for international usage?

hi, Alex !

you might want to give a try to an (ancient ?) xl4 macrofunction through a defined name-formula (i.e.)

name: MonthText
formula: =rept(index(get.workspace(37),20),4)

and then... =TEXT(DATE(2009,4,1),MonthText)

hth,
hector.

__ OP __
=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?



  #3   Report Post  
Old May 8th 09, 01:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 2
Default how format date with TEXT() function for international usage?

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.
  #4   Report Post  
Old May 8th 09, 06:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 high cpu usage, format cell, wrap text Michael S. Wall Excel Discussion (Misc queries) 9 October 16th 09 07:34 PM
About the usage of the function Keith Hui Excel Worksheet Functions 1 March 31st 09 04:12 PM
Working with times in military/international format. MidWest Yankee Excel Discussion (Misc queries) 2 April 8th 08 03:29 PM
Convert date + time text format to date format Paul Ho Excel Worksheet Functions 2 May 22nd 07 05:47 PM
TEXT function to format a date in English & French IanC Excel Worksheet Functions 2 December 16th 04 01:59 PM


All times are GMT +1. The time now is 09:38 AM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017