9

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?

3 Answers 3

15

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

8
  • 1
    Dave is spot on here Mar 17, 2014 at 12:57
  • 1
    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?
    – рüффп
    Mar 17, 2014 at 13:35
  • @ruffp, that is an excellent question - I suggest you start a new question for it though!
    – Dave
    Mar 17, 2014 at 13:35
  • 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.
    – рüффп
    Mar 18, 2014 at 8:24
  • 2
    Thanks, I've got crazy upon an .xls with language set to Finnish until I have seen you post
    – sberezin
    May 4, 2020 at 9:35
0

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.

1
  • I think doing that is equivalent to change them from the GUI (Control Panel) and not really what I want.
    – рüффп
    Jul 14, 2015 at 16:05
0

The easy fix, whether directly custom formatting a cell or using TEXT(), is to use a country code for a language you know the proper formatting codes for.

For instance, I am in the US, have a US version of Excel, and am familiar with its date code formats. So I'd want to use them and to ensure they "come out" regardless of anyone's Windows or Excel version, or the country they are in, I'd do it like the following (for TEXT(), let's say, but it'd be the same idea in custom formatting):

=TEXT(A1,[$-en-US]"dd-mm-yyyy")

The function would collect the value in A1, ask Excel to treat it as a date, Excel would and would say fine, it's cool (i.e.: the value is, say, 43857 and not "horse") because it is a positive number which is a requirement for anything to be treated as a date, and let the function move on to rendering it as a date in the manner prescribed. Rather than giving an #ERROR! as it would for "horse" or -6.

The function would then read the formatting string and see the language code. It would then drop the usual set of formatting codes it loaded upon starting up and load in the formatting codes for English ("en") and in particular, US English ("US"). The rest of the string uses codes from that set so it would interpret them properly and send an appropriate string back to TEXT() for it to display in the cell (and pass on to other formulas if such exist).

I have no way to test the following, but I assume that if one were to use a format that displayed day of the week names or month names, they would be from the same language set. In other words, Excel would not think that even though you specified a country and language that you still wanted, say, Dutch or Congolese month names. So that kind of thing would still need addressed, but would be an easy fix too just involving, say, a simple lookup one could add though it'd be "fun" setting up the lookup table for each language one wanted to accomodate...

However, the basic issue that arises with this problem in general, is very, very easily solved with the country codes. They aren't even hard or arcane anymore now that the [$-409] syntax has been replaced with things like [$-en-us] and [$-he-IL] and so on.

1
  • Although this seems to be the proper answer, it does not work for me at all. One thing is that the country code string should be inside the quotes. But even then, the "yyyy" portion is displayed literally for me. Using MSO 365 in German.
    – Douba
    Oct 24, 2022 at 12:19

You must log in to answer this question.

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