Concatenate Date In Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

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.

What Is Concatenate Date In Excel?

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.

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.

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

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.

Frequently Asked Questions

1. Where is the Concatenate Function found in Excel?

First, choose an empty cell - select the “Formulas” tab - go to the “Function Library” group - click the “More Functions” option drop-down - click the “Compatibility” option right-arrow - select the “Concatenate” function, as shown below.
Concatenate Date in Excel - FAQ 1

2. Is Merging and Combining using Concatenate in Excel different?

Merging and combining using Concatenate function are really different in Excel.
• Merging - It combines or joins 2 or more cells but will remove all cell contents except the first cell content. Here, we will lose data.
• Combining Using Concatenate - It combines or joins 2 or more cells along with the cell contents without any data loss. It is the same with the Ampersand (&) too.

3. How to Concatenate keeping date formatting in Excel?

To get the proper date format in the result cell, we can set the date format first itself. So that when we execute the Concatenate Date in Excel formula, the result cell is already in the desired date format.
Therefore, select the result cell, or the entire column of the result cells. Then, select the “Home” tab - go to the “Number” group - click the “Number Format” option drop-down - choose the required option as “Short Date” or “Long Date”, as shown below. Then the cell will get formatted to the default pre-set date format.

Concatenate Date in Excel - FAQ 3

Download Template

This article must help understand Concatenate Date in Excel with its formulas and examples. You can download the template here to use it instantly.