Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let say I have the following values in the cells

A1 : `=Now()` which displays 17.03.2014 13:45 
A2 : 12.03.2014

Both are in date format and I have the function in another cells like this:

B1 : `=TEXT(A1;"")`
B2 : `=TEXT(A2;"dd mm yyyy")`

In the first case I received a #VALUE Error in the cell In the second case I received : dd 03 yyyy like only the month is working.

I am under Windows 7 and Office 2013 Professional.

Any guess what it could be?

share|improve this question
up vote 8 down vote accepted

Your code works fine for me.

Although I have to use

B1 : =TEXT(A1,"")    
B2 : =TEXT(A2,"dd mm yyyy")

I am guessing the reason I use , instead of ; is due to the difference of language. The fault is more likely to be due to

For example, I know in Germany, I can't use dd because d (day) in German is tag, and as such it requires tt

I'm guessing the month is fine, based upon your dd 03 yyyy... Switzerland uses many languages I think and so I have no idea what to try, but, something like

share|improve this answer
Dave is spot on here – Raystafarian Mar 17 '14 at 12:57
You got it... but for me it is as in the French part of Switzerland. I found this stupid because my OS and Office are in English. I was expecting to make all the language specific (macro and VBA code) to be in English. It means if I give you my Excel file you will not see the same things because you do not have the same "localisation". Is there no way to overrides this 'wrong' behaviour? – ruffp Mar 17 '14 at 13:35
@ruffp, that is an excellent question - I suggest you start a new question for it though! – Dave Mar 17 '14 at 13:35
In the past I remember this language/localisation specific keyword are totally messy: I hade to use the function SOMME({range}) instead SUM({range} if the Excel was installed in French language. Again I though installing the software in English will make these *** ways disappear. – ruffp Mar 17 '14 at 13:38
@barryhoudini then you can ask Microsoft why they create such function. My goal was to make some formatting programmatically (or in a cell through a built-in function). The case was made simple on purpose to be in a proper format for the SU site. – ruffp Mar 18 '14 at 8:24

To use the english text-format of the excel TEXT function, while you have different international system settings (Control Panel/Region and Language settings), so that the command =TEXT("12/12/2000", "yyyy") will work right, change the register:

Key Name: HKEY_CURRENT_USER\Control Panel\International Value 25 Name: iCountry Type: REG_SZ Data: 44

Be careful, in any case, such function will return #VALUE! error anytime the file will be opened on any computer with different international settings.

share|improve this answer
I think doing that is equivalent to change them from the GUI (Control Panel) and not really what I want. – ruffp Jul 14 '15 at 16:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.