Concatenate Date In Excel

Publication Date :

Blog Author :

Edited by :

Download FREE Concatenate Date Excel Template and Follow Along!
Concatenate Date Excel Template.xlsx

Table Of Contents

arrow

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:

  1. First, we need to open the CONCATENATE formula.


    Using Concatenate Function step 2

  2. The first value we need to show here is the emp name. So, we must select the emp name as the first argument.


    Using Concatenate Function step 3

  3. 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.


    Using Concatenate Function step 4

  4. The third argument is a date, so select the joining date cell.


    Using Concatenate Function step 5

  5. The fourth argument is also not present in the data. So, type “for “.


    Using Concatenate Function step 6

  6. The final argument is the department, and select department cell.


    Using Concatenate Function step 7

  7. Now, we have the full sentence ready.


    Using Concatenate Function step 8

    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.

  8. First, we must edit the formula. Then, we need to apply the TEXT function in the third argument.


    Using Concatenate Function step 9

  9. The TEXT function’s first argument is VALUE. It may ask which value to format, so we must select the date cell here.


    Using Concatenate Function step 10

  10. 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.”


    Using Concatenate Function step 11

  11. Now, press the “Enter” button. As a result, we must have an accurate date value.


    Using Concatenate Function step 12

    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.

Frequently Asked Questions (FAQs)

1

1. Where is the Concatenate Function found in Excel?

Arrow down filled
2

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

Arrow down filled
3

3. How to Concatenate keeping date formatting in Excel?

Arrow down filled