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

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!

we have the same issue at our work and I found that if I cannot influence the localization of the windows then I use a formula which in your case looks like:

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

It is funny that "dd" and "mm" is the same.

You can now use 'e' instead of 'yyyy'. The e is the universal version of yyyy a