How to concatenate string with DATE()?

Question

In Excel, I'm trying to have a cell look something like:

by no later than August 27, 2012

When I try to concatenate a string with a DATE, for example:

="by no later than " & DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

I get an output like this:

by no later than 41118

How can I get a date to show up instead of an integer?

score 15 · Accepted Answer · edited May 23 '17 at 12:09

15

DATE builds a date timestamp. You need to convert that to a string. See this question for how to do so:

It would look something like this:

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())), "DD/MM/YYYY hh:mm:ss")

edited May 23 '17 at 12:09

Community

answered Jul 28 '12 at 05:14

Jonathon Reinhart

score 6 · Answer 2 · answered Jul 28 '12 at 11:23

6

You don't really need DATE function at all for today's date, you could use just

="by no later than "&TEXT(TODAY(),"mmmm d, yyyy")

answered Jul 28 '12 at 11:23

barry houdini

I was just using it as an example. I'm not actually using `TODAY`. – Louis Waweru Jul 28 '12 at 11:25
Ah.....OK, took you a little too literally...... – barry houdini Jul 28 '12 at 15:27

score -3 · Answer 3 · edited Mar 21 '14 at 07:59

-3

If you format the cell containing your number (41118) as Date, you'll get exactly 27.8.2012. Simple!

edited Mar 21 '14 at 07:59

Kashish Arora

answered Mar 21 '14 at 07:37

Buddy

3 Answers3