Excel TEXT formula doesn't convert 'yyyy' to a year

Kapé picture Kapé · Dec 5, 2014 · Viewed 28.9k times · Source

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

Kapé picture Kapé · Dec 5, 2014

All kudos go to @AxelRichter, thanks Axel!

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

So if you have a Dutch Windows and an English Excel version 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 will fix this, this is very annoying!