Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 5 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 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 at 13:35
    
@ruffp, that is an excellent question - I suggest you start a new question for it though! –  Dave Rook Mar 17 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 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 at 8:24

Your Answer

 
discard

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.