Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CELL("Format") function and Locale
Hi,
In cell B5 I have the contents of 01.03.2004 and the format is "MMMM JJJJ" (Germany settings), I would like to show a title of a column with the following text "YTD Rev März 2003 (GCIX)" but when a use opens the file a computer will UK settings the column should read "YTD Rev March 2003 (GCIX)" since I can't write the format directly into the formula because Excel want change the format, hence I'm trying to read the format of the cell using the cell information function but this only return D3 which is "mmm-jj" so how do I get Excel to use this format or even better the format of the cell. ="YTD Rev " & TEXT(EDATE(B5;-12);CELL("Format";B5)) & " (GCIX)" returns YTD Rev D3 (GCIX) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CELL("Format") function and Locale
Hi
to avoid this I would create a user defined function in VBA to return this value for your (as VBA would use the english notation). e.g. try public Function format_date(idate as date,iformat as string) format_date = Format(idate,iformat) end function Now use this formula in your worksheet (regardless of the local language) as follows: =FORMAT_DATE(EDATE(B5;-12);"mmm-yy") -----Original Message----- Hi, In cell B5 I have the contents of 01.03.2004 and the format is "MMMM JJJJ" (Germany settings), I would like to show a title of a column with the following text "YTD Rev März 2003 (GCIX)" but when a use opens the file a computer will UK settings the column should read "YTD Rev March 2003 (GCIX)" since I can't write the format directly into the formula because Excel want change the format, hence I'm trying to read the format of the cell using the cell information function but this only return D3 which is "mmm-jj" so how do I get Excel to use this format or even better the format of the cell. ="YTD Rev " & TEXT(EDATE(B5;-12);CELL("Format";B5)) & " (GCIX)" returns YTD Rev D3 (GCIX) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming | |||
TEXT(Cell1,"MMMM YYYY") to work in all locale. | Excel Programming |