Excel TEXT formula doesn t convert yyyy to a year

0 votes

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!

Mar 25, 2022 in Database by Edureka
• 13,690 points
1,040 views

1 answer to this question.

0 votes

It appears that if you have a Dutch Windows operating system but an English version of Excel (2013), the formulas will become jumbled. For example, I still have English formula names like TEXT (which would be TEKST in Dutch), but I have to use a colon in the formula instead of a comma. The TEXT property format text expects the Dutch format, which is different this year (jjjj instead of yyyy).

If you have Dutch Windows and English Excel, the right formula for some text followed by a formatted date is:

="A few words and a date: "

&TEXT(A2;"dd-mm-jjjj")

answered Mar 30, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
854 views
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
4,925 views
0 votes
1 answer

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

If anyone needs to do this in ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
1,043 views
0 votes
1 answer

Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
798 views
0 votes
1 answer

Convert Date to Text without losing the format in Excel?

The steps are as follows: Copy the dates ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
39,756 views
0 votes
1 answer

How to translate a BDS excel-formula to XBBG API for Python

The parameters are improperly specified by you. ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
2,349 views
0 votes
0 answers

How to split a string of text in excel based on a given word?

I have a list of combinations of ...READ MORE

Apr 4, 2022 in Database by gaurav
• 23,260 points
466 views
0 votes
1 answer

How to convert date to a format `mm/dd/yyyy`

Your data is already in varchar, thus ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,600 points
4,245 views
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,352 views
0 votes
1 answer

What is the Excel formula to countif text partially makes up a cell?

With Formulas, Countif Partial String/Substring Match We can ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
16,286 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP