Software

How to combine text and dates in the same cell

If you want to display both text and date data in one cell, you'll need to include an extra formatting step. See how to use the TEXT function to get the desired results.
Excel does not normally let you combine text and dates together in the same cell. For example, suppose you have entered the formulas shown in Figure A.

Figure A

dates

Figure B shows the results.

Figure B

display

While the value in A2 displayed correctly, the value in A1 did not. This is because Excel displays the actual date data entered into the cell until it's formatted. Thus, to show the data in A1 as a true date, we need add the TEXT function to change the format to text. When we replace the formula in B1 with

="Today is"&" "&TEXT(A1,"MM/DD/YYYY")
we get the results shown in Figure C.

Figure C

text function


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

14 comments
Crymson831
Crymson831

You can slightly shorten this by removing the quotes with the space in it (" ") and just adding the space after "is".

Your example:
="Today is"&" "&TEXT(A1,"MM/DD/YYYY")

Simplified:
="Today is "&TEXT(A1,"MM/DD/YYYY")

Not a big deal but makes it a little more obvious to people trying to understand what each part of this formula is representative of in my opinion. 

info
info

Again, Microsoft right hand doesn't talk to the left hand. In other applications, the function isn't "TEXT" but "FORMAT"...

LightVelocity
LightVelocity

Very nice. I wasn't aware of this. I just tried this and some variations and they work!! I am just curious why in your example you have mentioned a & " " appended to the string "Today is". The same thing can be accomplished by adding the space to the string itself; Did you have any reason for doing it this way?

sarcher
sarcher

Cool! I've learnt something new!

simon.freeman
simon.freeman

Mary - as you know, if you add the space after "is" so it reads eg "date is " or, for real elegance in some situations, "date is: " you do not need the &" "& which is a saving in typing not to be sneezed at (and helps reading the formula). I'd personally put in the space between "& and "text(..." for readability.

P∆UL L∆M∆N
P∆UL L∆M∆N

If it's only a fixed string you want to put before or after the value, you can use a format string to get the same result: "Today is" mm/dd/yyyy

Arcturus16a
Arcturus16a

You can also try: Date: CTRL + ; (Press and hold CTRL and then press semi-colon) to get the date in you pc's date format. Time: CTRL + SHIFT; (Press and hold CTRL and SHIFT then press semi-colon) to get the time in you pc's date format. Optionally, you can do both in the same cell. This places a static date or time in the cell which will not be updated. Too bad it doesn't work in Page Headers or Footers.

Graham Wickens
Graham Wickens

=CONCATENATE("Today is : ",(TEXT(TODAY(),"DDDD"))," the ",DAY(TODAY()),IF(DAY(TODAY())

simon.freeman
simon.freeman

p.laman - sorry didn't understand this. Can you show the entire formula you mean starting with the "=" - thanks.

ToR24
ToR24

That tip will be most useful when actively logging activities. Thanks.

fractalzoom
fractalzoom

p.laman's cell formatting solution is the most elegant. Here are the steps: 1. Select the cell(s) to display the combined value 2. Right-click the selection, choose Format Cells 3. Click on the Number tab, scroll down and click on Custom 4. In the "Type" text box, type the following: "Today is "m/d/yyyy 5. Click OK. With this formatting in place, you can place just the date value in the cell, and it will be displayed with the words "Today is" in front of it. Much cleaner and more elegant.

clifforde
clifforde

Thanks Fractal, really like your suggestion. Clean, simple and efficient. You also avoid another problem if using original suggestion...you lose the number "quality". If you want to perform calculations on the result, you can't as it is now text, per Help on TEXT in Excel, as stated below: Formatting a cell with an option on the Number tab (Cells command, Format menu) changes only the format, not the value. Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number. I entered date in 2 days, Fractal's suggestion, and was able to perform subtraction operation. See below. 19-Sep Today is 9/17/2008 2

Editor's Picks