3

I've got a problem with the text function in Excel (2007). It doesn't format the year, it just states yyyy instead of e.g. 2014.

If I have a cell with 23-7-2014 and use the formula =TEXT(A1,"dd-mm-yyyy") I get 23-7-yyyy.

My PC is set to using the dd-mm-yyyy format.

I'm using this function in a larger formula mainly consisting of merging multiple columns in a single cell: ="reg"&C1533&"_"&D1533&"_"&E1533&"_"&F1533&"_"&TEXT(G1533,"mm-dd-yyyy")

Here's a screenshot of the issue:

Screenshot of issue

5
  • I can't get excel to duplicate this behaviour. For me, =TEXT(A1, "dd-mm-yyyy") works as does =TEXT(A1, "mm-dd-yyyy"). It doesn't make a difference whether the original data is a date or just text, it works either way.
    – mcalex
    Jul 23 '14 at 9:10
  • What is your language on the PC?
    – Dave
    Jul 23 '14 at 9:12
  • @DaveRook The language is set to Dutch. I also tried changing the date formatting in "Region and Language" but still no luck. Jul 24 '14 at 8:51
  • @JvE010, and what are the values you are assembling in single cell, using the Formula ` "Reg"&C1533&,,,` !! Mar 10 '19 at 6:18
  • stackoverflow.com/questions/27306317
    – bers
    Jul 24 '20 at 10:41
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 accommodate ...

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.

I would point out though, that if the only difficulty is the year portion, the simplest fix is the one from user353435: just use "e" for the year portion of the formatting string. Simplest hands down. More than that? Just do the above.

1

I'm not sure this is the correct way to solve it, but this works on my machine with the dutch language.

In dutch the y (for year) is substituted for the j (for jaar).

TEXT(G1533, "mm-dd-jjjj")

Or ... you could resort to this:

CONCATENATE(TEXT(G1533, "mm-dd-"), YEAR(G1533))

Also see Excel TEXT function does not work properly

0

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.

1
  • Still no luck. Changed it to dd-mm-yyyy (was set to dd-M-yyyy). Jul 24 '14 at 8:50
0

It is enough to write e instead of yyyy:

text(a1;"e-mm-dd")

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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