Concatenate Date In Excel

Article byJeevan A Y
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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 cellsCombine CellsCombining or merging cells in Excel is a method of combining one or more cells into a single cell. The merge and center button is available in the Home tab's alignment section.read more.

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

[Note: Wherever we type a comma (,), we can replace it with operator.]

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.

You can download this Concatenate Date Excel Template here – Concatenate Date Excel Template

This article is a guide to Concatenate Date in Excel. We learn to combine desired cell values using Concatenate, Ampersand(&), examples & downloadable template. You may also look at these useful functions in Excel: –

Reader Interactions

Comments

  1. rajesh says

    Very well explained, I got stuck many times because of this.