What is wrong with my concatenate formula for date in excel?

To get the correct result, your Formula should be written like this:

=A1&", "&TEXT(B1,"[$-421]dd mmmm yyyy")

=CONCATENATE(A1,", ",TEXT(B1,"[$-421]dd mmmm yyyy"))

Both will return,,

N.B. Above shown Formulas are working properly even when in Cell B1 the DATE has entered as TEXT.

Note, Sometimes this Formula returns ZERO.

=A1&", "&text(B1,"[$-421]dd mmmm yyyy;@")

Somewhere, I found Date Format like this

MM/DD/YYYY;@

and Number Format like

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Edited:

But ;@ with Date and _(@) with Number doesn't makes any difference or scene unless is not been used properly.

Basically @ is a Text placeholder, which can be used to apply the Font Color to Text values.
Suppose this Formula is written in Cell C1,

=A1&", "&TEXT(B1,"[$-421]dd mmmm yyyy")

and then after if you apply this Format to Cell C1,

General; General; General;[Red] "Date is, "@

Or [Red] "Date is, "@

you get,

Rajesh S

Posted 2018-12-16T18:09:27.660

Reputation: 6 800

Your formula is the same as the OP's except it leaves out the ampersand, which serves a purpose (then you talk about that and say that it also works). A number of people commented that the OP's formula works. So almost all of the answer is saying that it ought to work (but the OP reports that it doesn't). You report sometimes getting a zero, which might be replicating the OP's problem, but then the answer doesn't explain why or how to fix it, which was the question. – fixer1234 – 2018-12-18T11:12:21.777

@fixer1234,, when I tried his Formula =A1&", "&text(B1,"[$-421]dd mmmm yyyy;@") given ZERO many times but when i removed the ;@ sign it works and what I've mentioned also. Somewhere I've found that the `;;;@' replaces ZERO with Blanks and since the Formula doesn't have any special feature to explain so I decided to concise my answer. Thanks for observation,, soon I'll edit the post☺ – Rajesh S – 2018-12-19T05:23:41.617

@fixer1234,, check the edited part show how to use @ with Formula. – Rajesh S – 2018-12-19T07:05:22.363

Most of the answer isn't responsive to the question, it's a lot of distracting stuff to read through. The @ in the question is a placeholder for text in the cell content, it has nothing to do with color. There's nothing obvious wrong with the OP's formula, it works for other readers. The OP didn't describe how it doesn't work. Your earlier observation that you sometimes got a zero was the only evidence that someone besides the OP got it to do anything unexpected. So if you can explain how you got a zero, that could possibly explain the OP's problem. But nothing in the answer does that. – fixer1234 – 2018-12-19T08:05:12.383

Your formulas both seem to work for me for the expected result you say you cannot get. What result are you getting and show what you'd like to get instead. Simply [edit] and add that detail as it might be helpful. – Pimp Juice IT – 2018-12-16T18:20:01.917

1Is the date in B1 possibly text? – Mark Fitzgerald – 2018-12-16T19:32:26.930

2It appears to work properly whether B1 contains a date or text. If text, it displays the text as entered. If a date, it uses the month name in Indonesian (code [$-421]), which appears as "Januari". – fixer1234 – 2018-12-17T00:08:48.557

The formulas work in my environment. What the wrong result did you get? Please provide the wrong result. – Lee – 2018-12-17T09:31:46.717

@fixer1234 - I've never seen the [$-421] stuff used before. What is that? Is there a list of countries that applies to? It seems to translate the dd in to whatever language the $-### numbers are? – BruceWayne – 2018-12-17T19:05:40.003

@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See https://stackoverflow.com/questions/894805/excel-number-format-what-is-409. Here's a sortable list of locale codes: https://www.science.co.il/language/Locale-codes.php

– fixer1234 – 2018-12-17T22:16:57.467

What is wrong with my concatenate formula for date in excel?

Answers