Excel TEXT formula doesn't convert 'yyyy&#

问题:

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!

回答1:

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!

回答2:

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.

回答3:

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

回答4:

For same problem when your system or keyboard is Danish then use åååå in place of jjjj. I also struggled a lot to find this key. However if anyone facing same problem apart from Dutch or Danish then you can check which key is correct. To find it right click on any cell and goto 'Format Cell' then select 'Custom' and find date related formats in your local language. There you can find which is the right key to be used for DATE format in your local language.

回答5:

Excel here obviously fails. TEXT should be language agnostic.

The workaround I found, especially if you have multiple cells to format:

1. Calculate the desired date format in a hidden cell or white colored:

=IF(TYPE(VALUE(TEXT(DATE_CELL;"YY")))=1;"DD.MM.YYYY";"ДД.ММ.ГГГГ")

If you need, it can be extended to support multiple regions like:

=IF(TYPE(VALUE(TEXT(DATE_CELL;"YY")))=1;"DD.MM.YYYY";IF(TYPE(VALUE(TEXT(DATE_CELL;"ГГ")))=1;"ДД.ММ.ГГГГ";"DD.MM.JJJJ"))

2. On cells where you need dates, use previous cell as second parameter:

="Date: " & TEXT(DATE_CELL; FORMAT_CELL)

回答6:

Solution: To check if "jjjj" format is working:

The date you want to display in a certain format is in A1

In cell A2 link a cel to the cel with the date you want displayed in a certain format with the following formula:

=TEXT(A1;"dd/mm/jjjj")

In a 3rd cel you then place the following formula:

=IF(RIGHT(A2;4)="jjjj";TEXT(A1;"yyyy");TEXT(A1;"jjjj"))

If the 4 digits from the right are "jjjj" this formula will display the date formatted as "yyyy" else the formatting "jjjj" will be used.

回答7:

This issue is still there on Excel 2016 (or Office 365, if you may). It is caused by different language settings in Windows. This happens for languages that have different year symbols (dutch is 'jjjj', portuguese is 'aaaa'). If you're experiencing this problem, try using your local Windows language's year format.