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)

Region and Language

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.

  • 5
    I cannot believe this has even been addressed on Excel. With this behaviour the result of the document is totally dependant on the computer opening the file, making the file contents change depending on the computer. I came across with this issue when opening a file originally using commas (,) for decimal values in a computer using dots (.) and formulae gave incorrect results...
    – AxeEffect
    Feb 26, 2017 at 16:17

11 Answers 11


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:

Public Function FMT$(ByVal Value, ByVal strFormat)
    FMT = VBA.Format$(Value, strFormat)
End Function

What this does is simply use VBA's own Format function instead of Excel's TEXT 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:

  1. Apply your desired date format to a cell, using the normal Cell Format dialog. This cell can be on a hidden sheet if you prefer--it does not need to be displayed to the end-user. Let's assume you applied the format yyyymmdd\_hhss to cell $A$1 (note that the underscore must be escaped as shown).
  2. Add the GetFormat function (shown below) to a VBA module in your workbook.
  3. Enter =GetFormat($A$1, TRUE) into another cell (e.g. $B$1)
  4. That function will return the localized version of the format string. So even though you originally formatted $A$1 with yyyymmdd\_hhss, when you open the workbook on a computer using the French language (for example), the function will show aaaammjj\_hhss.
  5. Now simply reference the second cell in all of your TEXT functions. For example: =TEXT(F22, $B$1).

Here's the VBA code:

Public Function GetFormat$(Cell As Range, Optional ByVal UseLocal As Boolean)
    If UseLocal Then
        GetFormat = Cell.NumberFormatLocal
        GetFormat = Cell.NumberFormat
    End If
End Function

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.


  • In the figure, Column 1 lists several representations of a single date with different formats applied.
  • Note that rows 2 and 3 use Excel's "system default" date formats. (These are indicated by a leading asterisk in the Format Dialog and they indicate that the user's default date format should be used.) Also note that row 5 uses a bracketed LCID, which forces the language used for month and day names to be English (different LCIDs can be used to specify other languages).
  • The second column shows the result of GetFormat(Cell, FALSE) for each cell in column 1. (Recall that FALSE for the second parameter causes the function to return the NON-localized formats).
  • The third column shows what GetFormat(Cell, TRUE) returns for each cell in column 2. (i.e. the localized formats).
  • The fourth column shows the result of the TEXT function using the original, raw date value and the localized result of 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 with TEXT 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.


  • 1
    Thanks a lot for your answer (even late) - Method 1 is the best for me and simple enough for all my cases. Very good and complete answer.
    – рüффп
    Jul 14, 2015 at 16:29
  • Could you plz explain how can I insert 28.11.2015 using the first method? Where and what should I write???????
    – Ilan
    Nov 22, 2015 at 12:13

For dates you can actually define a format that makes Excel use a certain locale. If you set the number format of the cell containing your date to something like

[$-409]m/d/yy h:mm AM/PM;@

Excel will display your date in a US locale (hex 409)

Gotcha: The YMD notation is localized and thus turns to JMT on a german system for example. So you might have to adapt the format string to your situation but i would expect it to behave like the formulas (autotranslate) on systems using a different language (as you seem to be aware of).

See here for some more details: https://stackoverflow.com/questions/894805/excel-number-format-what-is-409

and here for a list of locales: http://support.microsoft.com/kb/221435

  • 2
    But it does not answer my question. I want to be able to write dd/mm/yyyy in my Excel sheet even the OS wants "jj/mm/aaaa". I tried to change my OS locale and the output is changing, then Excel does not adapt according the user's Locale. If I write "jj.mm.aaaa" (as in French) and I change my locale to be US, the Excel shows: "jj.03.aaaa" which is not adapted.
    – рüффп
    Mar 24, 2014 at 8:41

Even though this question is a bit old, I came across another (simple!) solution and am posting here for the sake of completeness/reference.

  • I tried ricovox's answer successfully, but wanted a solution without VBA.

  • Tested MarinD's solution as well. But, as pointed out in the comments, if you need to cater for more than one country-specific format it can get unwieldy pretty fast.

  • And now to my solution: using Excel's native YEAR(), MONTH() and DATE() functions, wrapped in TEXT() to pad the zeros in front. With the Date in A1:


    gives me the date in YYYY-MM-DD format, irrespective if the current locale requires YYYY-MM-DD or AAAA-MM-JJ or any other localised names for year/month/date.

    Excel translates its native formulae automatically.


A simple way to solve the Excel locale date format problem is to use the CHOOSE function:



In these examples you will get the Day and Month in letters without using locale formats like "Mmmm" or "Dddd" or "Jjjj".

  • Simple as long you are managing one language (French in your case) but if you have to manage multiple anguages, your formula will become way too big.
    – рüффп
    Aug 3, 2015 at 12:06
  • I manage only one langage. The point of the use of CHOOSE is precisely to produce a one langage Excel sheet that will work on any other locale version of Excel. Aug 3, 2015 at 15:58
  • The big failure of Excel TEXT function is that the date format depend on your current Windows locale in Regional Settings. Example = =TEXT(TODAY();"YYYY-MM-DD") for US =TEXT(TODAY();"AAAA-MM-JJ") for FR So it is not possible to create a universal Excel sheet using TEXT. Use CHOOSE instead. Aug 3, 2015 at 16:08
  • At the end, a solution with another function with hard-coded values is not really helping more than hard-coding the date patterns. Personally I would go with VBA and the accepted answer. Thanks anyway for sharing your experience.
    – рüффп
    Aug 3, 2015 at 18:46

If your reference date is in cell A1:

  • That is a good workaround if you need to manage only one extra format... I am sure anyway my colleagues from Zurich or any other country which is not english nor french, this will still not work. If you have to make if(if(if... for every single format all around the world, it would be very difficult to manage.
    – рüффп
    Mar 17, 2015 at 22:39
  • That's right, but I could not find anything better without going through a VBA macro. I don't understand why Microsoft translate the functions and their properties (which is even worse because they do not translate them automatically when you open it with another language, as in our issue here). No programming language ever did that. Just English would have been enough
    – Sithered
    Mar 18, 2015 at 8:17

A very simple way to avoid the whole mess is to create a list of days of known dates, and use the list in your function.

  • 4
    Can you expand your answer to include an example?
    – Burgi
    Mar 30, 2016 at 9:26

I have now used a similar logic to the Method 2 given with the Macros. The Macros work, but usually when you send a file from one organization to another, the macros won't make it through the exchange or the antivirus won't let them run. So I have simply renamed ia cell as dateformat. In this cell I have simply entered the text dd/mm/yyyy. So when I am using the text function I am using the cell name for the format e.g. =text(C1,dateformat) and it seems to be working (precisey for Switzerland it works).



  • 2
    You say this works in Switzerland, but is it “French (Switzerland)”, “German (Switzerland)”, or is it English?  If it’s English, this doesn’t really contribute anything to this discussion.  If it’s not English, can you explain why =TEXT(C1,dateformat) would work (with the dateformat cell containing dd/mm/yyyy) when =TEXT(C1, "dd/mm/yyyy") doesn’t work?  (Or does it work?) Jan 29, 2018 at 17:31

Just dropping in another imho elegant alternative taken from: Stackoverflow answer by @Taosique

=IF(TEXT(1,"mmmm")="January",[some logic for English system],[some logic for non-English system])
  • It might not work. I don't know all languages but as long as the word for month doesn't start with "m" in some of the languages, this will probably not work at all (rendering a #VALUE error). Even if the word starts with "m" there are more than just 2 options here. Germany, Poland and Hungary each have a different formatting codes to name just a few.
    – Ister
    Mar 5, 2019 at 9:38
  • I assume hat the developer knows what language setting he is developing in (e.g. here English system) and which he wants his formula to work in/to translate it to. Should be no big deal to extend the IF for whatever language he/she is interested in.
    – GWD
    Mar 5, 2019 at 9:44
  • Well, I've listed those specific countries because I already had a case where I had to adapt to all those languages at the same time. There is also a limit of embedded IFs, however this is also easy to sidestep. I agree that the idea is good in general and its main advantage is no VBA needed.
    – Ister
    Mar 5, 2019 at 11:26

I know, late to the party, but looking into this myself I just found a working solution on ExcelBanter which doesn't need VBA.

The way to go is use the old Excel v4 macro system. It can still be accessed via named ranges or v4 macro sheets. It was a learning experience for me as well; a page on Excel off the grid and one on Ron de Bruin's site were nice introductions. The latter also has a link to a workbook with the same solution in it.

Basically, the old Excel v4 macro function GET.WORKSPACE can be used to get meta information about a lot of things, including the localized date format in use. GET.WORKSPACE(37) returns an array of all characters used for all sorts of separators etc, where index 19-20-21 hold the letter used for year,month and day formatting. So using INDEX(GET.WORKSPACE(37),19) returns "y" on English language settings, "j" on Dutch and "a" on Spanish. Combine that with a REPT to generate the 4-letter code and you're done. Problem is though that the old v4 functions (like GET.WORKSPACE) can only be a used by v4 style macro sheets and named ranges.


  • create a named range, and name it e.g. "MonthFormat"
  • give it the formula =REPT(INDEX(GET.WORKSPACE(37),19),4)
  • in your Text formula, use this MonthFormat name to specify the format, like =TEXT(A1, MonthFormat)

For a formula and macro-free solution, format the cell (Ctrl-1) as a Number->Date.

The first two listed format types on non-US locales -- prefixed with an asterisk -- localize automatically depending on where the document was opened and undoubtedly map to the VBA Format() "Short Date" and "Long Date", i.e., to whatever has been set in the Windows setting Region Date Format. The other formats do not re-localize (according to the help text).

So if you wanted to use fixed US English format, you would select Locale->English (United States) and e.g. 14-Mar-12 in the Format Cells dialog box.

Note that the English (United States) locale has a ISO8601-style date format yyyy-mm-dd, which was designed to be unambiguous across locales.


I can't believe nobody has offered up the named formats as a solution.

If you format a date and enter =TEXT(A1,"Short Date") it will automatically use the proper format based on the regional setting with zero issues between regions.

"Long Date" is also an acceptable entry and will work seamlessly across regions. It will also convert back to a proper date in VBA with cvdate regardless of version used.  

  • In Excel 2016, it will keep whatever regional formatting was assigned to it. If you're trying to compare dates with different regional formats, the comparison will fail. This is so bad it's probably a bug. Jul 17, 2018 at 19:36
  • Just tried this formular with a German Excel installation for the 2nd Februar 2004. It gives me: "00or2 Da22004" Oct 3, 2020 at 17:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .