Excel Fox
01-12-2015, 01:09 PM
I recently came across a situation where one of the Excel files had to be used by people in various countries. And it so happened that their regional language settings were completely different. One was English. And the other was another. The formula that I was using was to display the date from a particular cell in another format.
So I had 12th January 2015 as 1/12/15 in cell A1.
And =TEXT(A1,"dd mmm yyyyy") in B1. In the English setting this would result to 12 Jan 2015
But when you view this in another language setting, like German for example, you get your result as dd 00 yyyy :(
Even worse, if you use =TEXT(A1,"dd/mmm/yyyyy"), that will throw an error in non English settings.
Anyway, as a work around, I used the GET.CELL feature explained in http://www.excelfox.com/forum/f13/excel-macro-functions-get-cell-75/
Attaching a sample for reference. Pretty cool, huh! :)
Note: The GET. features of XLM (Excel 4.0 Macro) requires a macro enabled file.
So I had 12th January 2015 as 1/12/15 in cell A1.
And =TEXT(A1,"dd mmm yyyyy") in B1. In the English setting this would result to 12 Jan 2015
But when you view this in another language setting, like German for example, you get your result as dd 00 yyyy :(
Even worse, if you use =TEXT(A1,"dd/mmm/yyyyy"), that will throw an error in non English settings.
Anyway, as a work around, I used the GET.CELL feature explained in http://www.excelfox.com/forum/f13/excel-macro-functions-get-cell-75/
Attaching a sample for reference. Pretty cool, huh! :)
Note: The GET. features of XLM (Excel 4.0 Macro) requires a macro enabled file.