Excel TEXT formula doesn t convert yyyy to a year

Question

I want to combine some text with a date in Excel 2013.

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

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

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

But this shows Some text and a date: 30-10-yyyy and not Some text and a date: 30-10-2014

So or I do not understand how the TEXT formula works or is there some bug / issue here?

UPDATE: It looks like it's important that I have a Dutch version of Windows (7) but an English version of Excel (2013) which causes this issue!

Answer 1

It appears that if you have a Dutch Windows operating system but an English version of Excel (2013), the formulas will become jumbled. For example, I still have English formula names like TEXT (which would be TEKST in Dutch), but I have to use a colon in the formula instead of a comma. The TEXT property format text expects the Dutch format, which is different this year (jjjj instead of yyyy).

If you have Dutch Windows and English Excel, the right formula for some text followed by a formatted date is:

="A few words and a date: "

&TEXT(A2;"dd-mm-jjjj")