What is wrong with my concatenate formula for date in excel?
I've been in this trouble for some times.
I have "Oxford" in cell A1 and "02 January 2019" in cell B1 and I want to merge these two cells separated with a comma and single space.
I've tried
=A1&", "&text(B1,"[$-421]dd mmmm yyyy;@")
and
=concatenate(A1,", ",text(B1,"[$-421]dd mmmm yyyy;@"))
but none of them worked.
Do you have any idea what was wrong with my formula?
microsoft-excel date-format
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
asked Dec 16 at 18:09
Eve Hermes
1
|
show 1 more comment
I've been in this trouble for some times.
I have "Oxford" in cell A1 and "02 January 2019" in cell B1 and I want to merge these two cells separated with a comma and single space.
I've tried
=A1&", "&text(B1,"[$-421]dd mmmm yyyy;@")
and
=concatenate(A1,", ",text(B1,"[$-421]dd mmmm yyyy;@"))
but none of them worked.
Do you have any idea what was wrong with my formula?
microsoft-excel date-format
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
asked Dec 16 at 18:09
Eve Hermes
1
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
Dec 16 at 18:20
1
Is the date in B1 possibly text?
– Mark Fitzgerald
Dec 16 at 19:32
2
It 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
Dec 17 at 0:08
The formulas work in my environment. What the wrong result did you get? Please provide the wrong result.
– Lee
Dec 17 at 9:31
1
@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See stackoverflow.com/questions/894805/…. Here's a sortable list of locale codes: science.co.il/language/Locale-codes.php
– fixer1234
Dec 17 at 22:16
|
show 1 more comment
I've been in this trouble for some times.
I have "Oxford" in cell A1 and "02 January 2019" in cell B1 and I want to merge these two cells separated with a comma and single space.
I've tried
=A1&", "&text(B1,"[$-421]dd mmmm yyyy;@")
and
=concatenate(A1,", ",text(B1,"[$-421]dd mmmm yyyy;@"))
but none of them worked.
Do you have any idea what was wrong with my formula?
microsoft-excel date-format
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
asked Dec 16 at 18:09
Eve Hermes
1
I've been in this trouble for some times.
I have "Oxford" in cell A1 and "02 January 2019" in cell B1 and I want to merge these two cells separated with a comma and single space.
I've tried
=A1&", "&text(B1,"[$-421]dd mmmm yyyy;@")
and
=concatenate(A1,", ",text(B1,"[$-421]dd mmmm yyyy;@"))
but none of them worked.
Do you have any idea what was wrong with my formula?
microsoft-excel date-format
microsoft-excel date-format
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
asked Dec 16 at 18:09
Eve Hermes
1
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
asked Dec 16 at 18:09
Eve Hermes
1
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
edited Dec 16 at 18:30
Pierre.Vriens
1,20561218
1,20561218
asked Dec 16 at 18:09
Eve Hermes
1
asked Dec 16 at 18:09
Eve Hermes
1
asked Dec 16 at 18:09
Eve Hermes
1
1
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
Dec 16 at 18:20
1
Is the date in B1 possibly text?
– Mark Fitzgerald
Dec 16 at 19:32
2
It 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
Dec 17 at 0:08
The formulas work in my environment. What the wrong result did you get? Please provide the wrong result.
– Lee
Dec 17 at 9:31
1
@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See stackoverflow.com/questions/894805/…. Here's a sortable list of locale codes: science.co.il/language/Locale-codes.php
– fixer1234
Dec 17 at 22:16
|
show 1 more comment
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
Dec 16 at 18:20
1
Is the date in B1 possibly text?
– Mark Fitzgerald
Dec 16 at 19:32
2
It 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
Dec 17 at 0:08
The formulas work in my environment. What the wrong result did you get? Please provide the wrong result.
– Lee
Dec 17 at 9:31
1
@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See stackoverflow.com/questions/894805/…. Here's a sortable list of locale codes: science.co.il/language/Locale-codes.php
– fixer1234
Dec 17 at 22:16
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
Dec 16 at 18:20
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
Dec 16 at 18:20
1
1
Is the date in B1 possibly text?
– Mark Fitzgerald
Dec 16 at 19:32
Is the date in B1 possibly text?
– Mark Fitzgerald
Dec 16 at 19:32
2
2
It 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
Dec 17 at 0:08
It 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
Dec 17 at 0:08
The formulas work in my environment. What the wrong result did you get? Please provide the wrong result.
– Lee
Dec 17 at 9:31
The formulas work in my environment. What the wrong result did you get? Please provide the wrong result.
– Lee
Dec 17 at 9:31
1
1
@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See stackoverflow.com/questions/894805/…. Here's a sortable list of locale codes: science.co.il/language/Locale-codes.php
– fixer1234
Dec 17 at 22:16
@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See stackoverflow.com/questions/894805/…. Here's a sortable list of locale codes: science.co.il/language/Locale-codes.php
– fixer1234
Dec 17 at 22:16
|
show 1 more comment
1 Answer
1
active
oldest
votes
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,
answered Dec 18 at 7:33
Rajesh S
3,7021522
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
Dec 18 at 11:12
@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
Dec 19 at 5:23
@fixer1234,, check the edited part show how to use@with Formula.
– Rajesh S
Dec 19 at 7:05
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
Dec 19 at 8:05
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1385051%2fwhat-is-wrong-with-my-concatenate-formula-for-date-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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,
answered Dec 18 at 7:33
Rajesh S
3,7021522
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
Dec 18 at 11:12
@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
Dec 19 at 5:23
@fixer1234,, check the edited part show how to use@with Formula.
– Rajesh S
Dec 19 at 7:05
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
Dec 19 at 8:05
add a comment |
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,
answered Dec 18 at 7:33
Rajesh S
3,7021522
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
Dec 18 at 11:12
@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
Dec 19 at 5:23
@fixer1234,, check the edited part show how to use@with Formula.
– Rajesh S
Dec 19 at 7:05
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
Dec 19 at 8:05
add a comment |
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,
answered Dec 18 at 7:33
Rajesh S
3,7021522
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,
answered Dec 18 at 7:33
Rajesh S
3,7021522
edited Dec 19 at 7:15
answered Dec 18 at 7:33
Rajesh S
3,7021522
answered Dec 18 at 7:33
Rajesh S
3,7021522
answered Dec 18 at 7:33
Rajesh S
3,7021522
3,7021522
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
Dec 18 at 11:12
@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
Dec 19 at 5:23
@fixer1234,, check the edited part show how to use@with Formula.
– Rajesh S
Dec 19 at 7:05
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
Dec 19 at 8:05
add a comment |
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
Dec 18 at 11:12
@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
Dec 19 at 5:23
@fixer1234,, check the edited part show how to use@with Formula.
– Rajesh S
Dec 19 at 7:05
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
Dec 19 at 8:05
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
Dec 18 at 11:12
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
Dec 18 at 11:12
@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
Dec 19 at 5:23
@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
Dec 19 at 5:23
@fixer1234,, check the edited part show how to use
@ with Formula.– Rajesh S
Dec 19 at 7:05
@fixer1234,, check the edited part show how to use
@ with Formula.– Rajesh S
Dec 19 at 7:05
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
Dec 19 at 8:05
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
Dec 19 at 8:05
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1385051%2fwhat-is-wrong-with-my-concatenate-formula-for-date-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Dec 16 at 18:20
1
Is the date in B1 possibly text?
– Mark Fitzgerald
Dec 16 at 19:32
2
It 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
Dec 17 at 0:08
The formulas work in my environment. What the wrong result did you get? Please provide the wrong result.
– Lee
Dec 17 at 9:31
1
@BruceWayne, yeah, that was a new one for me, too. Had to research it, and it's a bit obscure. See stackoverflow.com/questions/894805/…. Here's a sortable list of locale codes: science.co.il/language/Locale-codes.php
– fixer1234
Dec 17 at 22:16