Excel formula: Join date and text

Generic formula

="text"&TEXT(date,format)

Explanation

To join a date with text, you can use concatenation with the TEXT function to control the date format. In the example shown, the formula in E4 is:

="The date is "&TEXT(B4,"dddd, mmmm yyyy")

How this formula works

The core of this formula is concatenation using the ampersand (&) operator:

="The date is"&date

However, if you simply join a date with text and don't control the date format, the date will revert to it's raw serial number format, and the result will look like this:

"The date is 42887".

To control the date format, use the TEXT function, which is designed to convert a number into text using a specified number format. This article shows a list of codes you can can use for date formats.

Author

Dave Bruns

Related formulas

Get day name from date

If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date, there are several options depending on your needs. Do you just want to display the day name? If you only want to display a day name, you don't need a formula – you can use a...

Get month name from date

If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs. Do you just want to display the month name? If you only want to display a month name, you don't need a formula –...

Convert date to text

If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function. The TEXT function can use patterns like "dd/mm/yyyy", "yyyy-mm-dd", etc. to convert a valid date to a text value. See table below for a list of...

Join cells with comma

To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. In the example shown, the formula in G5 is: =...

Related functions

Excel TEXT Function

The Excel TEXT function returns a number in a given number format, as text. You can use the TEXT function to embed formatted numbers inside text.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.