Menu
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!
525
Sometimes you can face a common problem that the return value of the YEAR function is not any numerical value, but it looks like a date such as “01/01/1900”. Most likely, this problem will arise when you have your cell format set as 'date. ' To correct this problem, you have to set the format of the cell as 'general.
Imported Dates Are Text Data And that's why the imported dates won't change format -- Excel will not apply number formatting to text. Here are a few more signs that the items in column C are being treated as text: The items are left-aligned -- dates (numbers) are right-aligned by default.
Possible cause 1: Cells are formatted as text Cause: The cell is formatted as Text, which causes Excel to ignore any formulas. This could be directly due to the Text format, or is particularly common when importing data from a CSV or Notepad file. Fix: Change the format of the cell(s) to General or some other format.
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!
152
You can now use 'e' instead of 'yyyy'. The e is the universal version of yyyy a
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
