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)

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)
.