Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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;"dd.mm.yyyy")`
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,"dd.mm.yyyy")    
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 dd.mm.yyyy

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 tt.mm.jjjj

share|improve this answer
1  
Dave is spot on here – Raystafarian Mar 17 '14 at 12:57
    
You got it... but for me it is jj.mm.aaaa 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
1  
@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

You must log in to answer this question.

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