Excel TEXT function does not work properly

Question

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?

Solution

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

Comments

[Raystafarian]

Dave is spot on here

[рüффп]

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?

[Dave]

@ruffp, that is an excellent question - I suggest you start a new question for it though!

[рüффп]

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.

[рüффп]

and nothing state this in this official doc: goo.gl/byIktN

[barry houdini]

Why use TEXT function at all? unless you specifically need the date formatted as text it's usually better to just use cell formatting - and unlike the "dd.mm.yyyy" part of TEXT function the cell format will adjust according to the region

[рüффп]

@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.

[рüффп]

I think doing that is equivalent to change them from the GUI (Control Panel) and not really what I want.

[sberezin]

Thanks, I've got crazy upon an .xls with language set to Finnish until I have seen you post