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!
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!