Join date and text

★ 20 minutes read

In this tutorial, you will learn how to join date and text using TEXT function and ampersand operator “&”. To join date and text you must use TEXT function because dates are interpreted as numbers in Excel. TEXT function converts date from number to date in the specific date format. Due to numerous date formats in Excel TEXT function, you have huge flexibility to join date and text in the form that is suitable for you.

Join date and text

Formula

="text"&TEXT(date,"date format")

Explanation

TEXT function converts numbers to text in a specific format. The formula has two arguments: value, which is in our formula date and format_text which is in our formula date format. Since Excel interprets dates as numbers, we should use TEXT function to convert a number in date. If you put in date format “dddd,mmmm,yyyy” formula will return the day name, month name and year from the selected cell with the date.

You can join date and text using ampersand operator &. In concatenation process text is always under quotations, while ampersand operator & is the link between two texts that will be joined. Since TEXT function is the other part of the concatenation process, we should not put that function under Quotations. Also, remember to use spaces at the end of the text because formula will join date and text without spaces.

Example

In the example below, we want to join date and text in the different ways. In the column Format text, you can find several date formats for the TEXT function part of the formula. The formula in the cell C3 looks like:

="Today is "&TEXT(B3,"dddd, mmmm yyyy")

 

 

Figure 1. Join date and text

Let’s explain the TEXT function part of the formula. The first parameter of the function is the date in the cell B3. The second part of the function is the date format under Quotations. Because we want full day name, month name and year, formula syntax for date format looks like “dddd, mmmm yyyy”.

Since we want to join text with formula (TEXT), the text should always be under quotations and formula not. To join date and text use ampersand operator & as the link between two part of the formula. Finally, the final formula output is “Today is Saturday, January 2018”.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar