Excel TEXT Formula Does Not Convert 'yyyy' to Year

Question

I want to combine text with date in Excel 2013.

Let's say I have cell A2 with a date like 30-10-2014. I tried to add a date after the text using this formula: ="Some text and a date: "&A2

But the output shows the date as a number: some text and date: 41942

So, I tried it with TEXT : ="Some text and a date: "&TEXT(A2;"dd-mm-yyyy")

But it shows some text and date: 30-10-yyyy, not some text and date: 30-10-2014

So or I don’t understand how the TEXT formula works or is there some kind of error / problem here?

UPDATE It seems important that I have a Dutch version of Windows (7), but an English version of Excel (2013) that is causing this problem!

Answer 1

All rewards go to @AxelRichter, thanks Axel!

It looks like if you have Dutch Windows but the English version of Excel (2013), the formulas are mixed. For example, I still have English formula names, such as TEXT (which would be TEKST in Dutch), but still have to use a colon instead of a comma in the formula. The format_text TEXT value still expects the Holland format, which is different from the year (jjjj instead of yyyy).

So, if you have Dutch Windows and the English version of Excel, the correct formula for some text, followed by a formatted date, would be:

="Some text and a date: "&TEXT(A2;"dd-mm-jjjj")

I hope Microsoft fixes this, it is very annoying!

Answer 2

we have the same problem in our work, and I found that if I can not affect the localization of windows, then I use a formula that in your case looks like this:

 ="Some text and a date: "&TEXT(A2;"dd-mm-")&YEAR(A2) 

It's funny that "dd" and "mm" are one and the same.

Answer 3

For the same problem, when your system or keyboard is Danish, use instead of ååå instead of jjjj. I also tried a lot to find this key. However, if anyone faces the same problem besides Dutch or Danish, then you can check which key is correct. To find it, right-click on any cell and go to "Format Cell", then select "Custom" and find the corresponding dates in your local language. There you can find what is the correct key to use for the DATE format in your local language.

Answer 4

Excel is clearly not working here. TEXT must be a linguistic agnostic.

The workaround I found, especially if you have several cells for formatting:

• Calculate the desired date format in a hidden cell or in white:

=IF(TYPE(VALUE(TEXT(DATE_CELL;"YY")))=1;"DD.MM.YYYY";"..")

If you need it, it can be expanded to support several regions, such as:

=IF(TYPE(VALUE(TEXT(DATE_CELL;"YY")))=1;"DD.MM.YYYY";IF(TYPE(VALUE(TEXT(DATE_CELL;"")))=1;"..";"DD.MM.JJJJ"))

2. In cells where you need dates, use the previous cell as the second parameter:

="Date: " & TEXT(DATE_CELL; FORMAT_CELL)

Answer 5

Decision. To check if the "jjjj" format works:

The date you want to display in a specific format is in A1 format

In cell A2, associate cel with cel with the date you want to display in a specific format with the following formula:

 =TEXT(A1;"dd/mm/jjjj") 

In the third part, you then put the following formula:

 =IF(RIGHT(A2;4)="jjjj";TEXT(A1;"yyyy");TEXT(A1;"jjjj")) 

If the 4 digits on the right are “jjjj”, this formula will display a date formatted as “yyyy”, otherwise the formatting will be “jjjj”.

Answer 6

This issue still exists in Excel 2016 (or Office 365, if you can). This is caused by various language settings in Windows. This happens for languages ​​with different characters of the year (Dutch - "jjjj", Portuguese - "aaaa"). If you have this problem, try using the local Windows language format.

Answer 7

Now you can use "e" instead of "yyyy". E is the universal version of yyyy a