According to this question I have the following problem:
I want to use some Excel function (not the cell formatting) like TEXT(A1, {date_pattern})
But the person who answer my previous question make me found that the date pattern change according to the Windows Regional Settings.
However, my OS (Windows 7) is in English and the Office suite as well.
By looking in my Regional settings it show even a pattern using English notation (dd.MM.yyyy)
I want to know if there is any way to disable such behaviour from Excel, meaning I want to always use the English patterns and never the localized ones because I do not want the behaviour of my Excel sheet to change according to localisation of the reader.
A simple case would be reformatting some date field to a computer centric way like this: "yyyymmdd_hhss" this is recognized universally and can be sorted up and down easily.
But as I am in the French part of Switzerland part I should write "aaaammjj_hhss" and if I send this Excel to a colleague in Zürich he would not be able to see the proper date as he got the Swiss German localization (his excel would expect "jjjjmmtt_hhss")
We were clever enough to install all windows and office in English but we still face problem like this because this link to the OS regional settings.
For me the changing Windows Settings is not an option because all the other programs are using this settings.
Best Answer
This question is a bit old, but to date it does not seem to have an answer. I've also seen similar questions on a number of sites, but have not yet found any answer that involves only built-in Excel functions. However, it is quite easy to solve this with VBA. You don't even have to know how to program to do this, because the required function is so simple.
With your workbook open, simply press Alt+F11 (to open the VBA editor) Then Click the menu item Insert > Module In the new VBA module, enter the following:
What this does is simply use VBA's own
Format
function instead of Excel'sTEXT
function. This is exactly what you want.This new function will format any date (or number) according to whatever format string you specify. It will interpret the format string using the standard (en-US) notation, regardless of the operating system's regional settings.
To use this in your workbook, simply type
=FMT(A1, "yyyymmdd_hhss")
instead of=TEXT(A1, "yyyymmdd_hhss")
. (Of course you can change the cell reference and format string as necessary.)By the way, you can name the function whatever you want. I chose
FMT
because it was short and because the function can format both numbers and dates. But you can choose something more descriptive if you want. Just remember to use the same name in your worksheet as in the VBA code.Note that the VBA format strings are not exactly the same as Excel's custom format strings (for example, use "n" instead of "m" for minutes), but they are very similar. Look here for details, or search MSDN for "Format Function (Visual Basic for Applications)". Scroll to the bottom to see the various date format specifiers.
Method 2
Another approach that also uses VBA, but might actually be easier to maintain, is the following:
yyyymmdd\_hhss
to cell $A$1 (note that the underscore must be escaped as shown).GetFormat
function (shown below) to a VBA module in your workbook.=GetFormat($A$1, TRUE)
into another cell (e.g. $B$1)yyyymmdd\_hhss
, when you open the workbook on a computer using the French language (for example), the function will showaaaammjj\_hhss
.TEXT
functions. For example:=TEXT(F22, $B$1)
.Here's the VBA code:
This allows us to "inspect" the cell you originally formatted ($A$1) to retrieve the localized format-string. That string will represent the same format you applied, but it will use the correct letters for TEXT to interpret ("j" instead of "d" for example), so the displayed value of the dates will be constant across all locales. If you wanted to use only one date format for your whole workbook, you would only need to do steps 1-4 once. Then repeat step 5 (the TEXT function) in all the cells where you currently use it, but instead of hard-coding a format, you would simply reference the cell that contains the localized format-string ($B$1 in the example instructions).
Note that the second argument to
GetFormat
tells the function whether or not to return the localized version (which depends on regional settings) or the "standard" version (which is always based on en-US).Here are some screen-shots that might make this more clear.
GetFormat(Cell, FALSE)
for each cell in column 1. (Recall thatFALSE
for the second parameter causes the function to return the NON-localized formats).GetFormat(Cell, TRUE)
returns for each cell in column 2. (i.e. the localized formats).GetFormat
to re-produce the format shown in column 1. Note however that NO number formatting was applied to this column. The values are a direct result of the TEXT function.The results for the English (US) case above are not very interesting, but you can compare them with the following results that were obtained by changing the Regional Settings of my operating system to various other locales. Importantly, note that by using
GetFormat
in combination withTEXT
we are able to retain a constant result for numeric formats (those that do not include day or month names) across all locales. And by constraining the language using an LCID (as in row 5) we can even retain a constant format when including day and month names as well.This method works for most locales, however it should be noted that the scripts used to represent the Hindu-Arabic numerals are NOT the same in all locales. Therefore regional settings like those of the Nepali (India) locale will result in date formats that "look" different than en-US dates. But these actually are in the same "format" in terms of the positions of numbers--they just use different symbols for the numbers.