Software

Concatenate date cells with text cells in Excel

Mary Ann Richardson explains that when concatenating date and text cell data in an Excel formula, you need to convert date cells to text to avoid unexpected results.

When concatenating—using a formula to combine data and/or text from one or more cells into one cell—date cells with text cells in an Excel formula, you must first convert the referenced date cells to text; otherwise, you end up with an unexpected result. For example, suppose cell H11 contained the text Due Date: and cell H12 contained a formula that calculated the date. H12 is correctly formatted for the date data type, m/d/yyyy. If you use the formula =H11&H12 to concatenate these cells, the result comes back with the serial date (such as, Due Date: 39054).

Because Excel ignores the formatting of H12, Excel returns the serial date unless the contents of H12 are converted to text before concatenating, as shown in the following formula:

=H11&TEXT(H12," mmmm d, yyyy")

The correct result of this formula is Due Date: December 3, 2006.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent 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.

7 comments
tali.tv
tali.tv

can also be combined with the =TODAY() function as below: [i]=CONCATENATE("Today's date is ",TEXT(TODAY(),"mm/dd/yyyy"))[/i] (or [i]=CONCATENATE("Today's date is ",TEXT(TODAY()[b]-1[/b],"mm/dd/yyyy"))[/i] etc)

ArchiveRoj
ArchiveRoj

Just used this on Excel 2010 and it's still working like a dream, thanks cristianlf

p.vidal
p.vidal

It's not working with Excel 2002. Do you know another solution ?

basil.cinnamon
basil.cinnamon

You can achieve the same result by selecting the date cell and Format... Cells... Custom.. and entering under Type: "Due Date" m/d/yy, although strictly speaking it is not a concatenation with another cell that may contain varying content.

PeterSS
PeterSS

One of the problems with converting the date to text is that it no longer sorts in date order. Once you have generated your date, you may want to convert it back to date format using the DATEVALUE() function. These will sort correctly.

cristianlf
cristianlf

just use excel function concatenate like this ie.: A1=[01/01/2011] B1=[Time to go to ...] C1=[=concatenate(Text(A1,"dd/mm/yyyy")," ",B1)] regards,

Level
Level

Thanks for the tip, basil. I hadn't thought of that.

Editor's Picks