Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT(Cell1,"MMMM YYYY") to work in all locale.
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" ************** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT(Cell1,"MMMM YYYY") to work in all locale.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT(Cell1,"MMMM YYYY") to work in all locale.
Thanks very much, I'll read more carefully next time
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine text & date formula to show mmmm dd, yyyy? | Excel Worksheet Functions | |||
How to change "xxx CR" and "y,yyy DB" to "+xxx" and "-yyyy" | Excel Worksheet Functions | |||
Convert text "date" (DDMMMYY) to datetime value of YYYY-MM-DD - ho | Excel Discussion (Misc queries) | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming |