Concatenate Date In Excel

Publication Date :

01 Feb, 2019

Blog Author :

Edited by :

Reviewed by :

Download FREE Concatenate Date Excel Template and Follow Along!

Concatenate Date Excel Template.xlsx

Table Of Contents

What Is Concatenate Date In Excel?

Concatenate Date in Excel helps users to combine or join a cell value with a date with any desired related information.
We can perform Excel Concatenate Date with other values with the help of the Ampersand (&) or the inbuilt Concatenate Function in Excel.

For example, using =“ABC”&NOW() will give us the output as ABC14/09/2019. Similarly, we can use the Concatenate Function.

One might wonder if combining dates is easy when we already know how to connect two or more cell string values. However, merging dates in Excel differs because it will not give us the desired results. So, let us go ahead and learn how to combine dates in Excel.

Key Takeaways

Concatenate Date in Excel helps users to combine date values in a cell, in the right format, along with the required information such as, important details or in the form of a sentence.
We can combine 2 or more cells with any values using the Ampersand (&) and the CONCAT function.
The CONCAT function works with Excel 2016 or above version, and the CONCATENATE function is available as the Compatibility function. Therefore, if we are working on the earlier versions of Excel, we must use the CONCATENATE function.

How To Concatenate Date In Excel?

We can Concatenate Date in Excel in two ways, namely,

Combine Text with Dates Using Concatenate Function.
Concatenate Text with Dates Using Alternative Method.

Method #1 – Combine Text with Dates Using Concatenate Function

Let us consider that you are a recruiter in a company and have recruited the below employees in the current month. You have their employee ID, name, salary, joining date, and respective department, as shown in the below image.

Using Concatenate Function step 1

We will frame a sentence for each employee as “Raju joined the company on 25-May-2017 in the sales department” using the Concatenate Function.

The steps are as follows:

First, we need to open the CONCATENATE formula.
The first value we need to show here is the emp name. So, we must select the emp name as the first argument.
The second argument is not present in the data. So, we need to type manually. So, type “joined the company on” in the double-quotes. That will be common for all the cells.
The third argument is a date, so select the joining date cell.
The fourth argument is also not present in the data. So, type “for “.
The final argument is the department, and select department cell.
Now, we have the full sentence ready.

Notice the date part here. We do not have an accurate date here. After using concatenate in Excel, the formula considers the date as the number.
We need to make the number a date format using the TEXT function in excel.
First, we must edit the formula. Then, we need to apply the TEXT function in the third argument.
The TEXT function’s first argument is VALUE. It may ask which value to format, so we must select the date cell here.
The final part of the TEXT function is FORMAT TEXT in excel, i.e., the value we have selected in what format we need. In this case, we need the format as the date. So, we must mention the date format as “DD-MMM-YYYY.”
Now, press the “Enter” button. As a result, we must have an accurate date value.

The output is shown above with the proper sentence formation. We must use the TEXT function to format the date and the time when we are combining. The TEXT function allows us to modify the cell-based format to our requirements.

Method #2 – Concatenate Text with Dates Using Alternative Method

We have learned the Concatenate Function to combine cells. We have an alternative method to do so.

The “&” (ampersand) is the symbol we use to combine cells.

Combine Text with Dates 1

We need to remove the Concatenate Function. To separate each argument, we can use the “&” symbol, as shown in the image below.

Combine Text with Dates 2

Important Things To Note

We can select only one cell as the argument. We cannot choose the range of cells in the Concatenate Function.
Using the TEXT function, we can modify the numbers to the date, time, or format we want.
Ampersand is the alternative way we can combine instead of concatenating functions.