Home » Excel, VBA & Power BI » Excel Resources » Concatenate Date in Excel

Concatenate Date in Excel

Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

To concatenate date in excel with other values we can use & operator or also known as concatenate operator or the inbuilt concatenate function in excel, for example, if we use =”ABC”&NOW() this will give us the output as ABC14/09/2019 as a result and in similar fashion we can use the concatenate function.

How to Concatenate Date in Excel?

In this article, I will take you through the concept of merging dates with concatenate functionConcatenate FunctionThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more. You must be wondering whats the big deal about merging dates when you already know how to merge two or more cells string values.

Yes, it is different when you are merging dates in excel because it won’t give you the exact result that you want. Ok, let’s go ahead and learn how to merge dates in excel.

#1 – Combine Text with Dates Using Concatenate Function

Now we know how to VBA ConcatenateVBA ConcatenateIn VBA, concatenation refers to joining two values or strings together with the "&" symbol (ampersand operator). We must include spaces when using the & operator, else VBA will interpret it as long.read more. Assume you are working as a recruiter in a company, and you have recruited below employees in the current month.

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

You have their employee ID, Name, Salary, Joining Date, and their respective department.

Using Concatenate Function step 1

Now you need to frame a sentence for each employee like the below.

Raju joined the company on 25-May-2017 for Sales Department.

Yes, you need to use the CONCATENATE function to frame a sentence like this.

  1. Open the concatenate formula.


    Using Concatenate Function step 2

  2. First value we need to show here is the emp name, so select the emp name as the first argument.


    Using Concatenate Function step 3

  3. The second argument is not there in the data; we need to type manually. So type “joined the company on ” in the double-quotes. This will be common for all the cells.


    Using Concatenate Function step 4

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


    Using Concatenate Function step 5

  5. The Fourth argument is also not there in the data. Type “for. “


    Using Concatenate Function step 6

  6. Final argument is the department and select department cell.


    Using Concatenate Function step 7

  7. We have the full sentence ready.


    Using Concatenate Function step 8

    Oh oh, hang on notice the date part here. We don’t have the accurate date here; after using concatenate in excel, the formula thinks date as the number, not the date.

    We need to make the number to a date format using the TEXT function in excel.

  8. Edit the formula; in the third argument, apply the TEXT function.


    Using Concatenate Function step 9

  9. TEXT function’s first argument is VALUE. It is asking which value to be formatted, so select the date cell here.


    Using Concatenate Function step 10

  10. The final part of the TEXT function is FORMAT TEXT in excelFORMAT TEXT In ExcelText formatting in Excel include changing the colour, font name, font size, alignment, font appearance in bold, underlining, italic, background colour of the font cell, and so on.read more i.e., the value we have selected in what format we need. In this case, we need the format as date, mention the date format as “DD-MMM-YYYY.”


    Using Concatenate Function step 11

  11. Now, hit the enter button. We must have got accurate date value.


    Using Concatenate Function step 12

    Oh, yes, we have an accurate sentence. We need to use the TEXT function to format the date, the time when we are combining. TEXT function allows us to modify the format of the cell-based on our requirements.

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

“&” (ampersand) is the symbol we need to 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 into one.

Combine Text with Dates 1

We need to remove the CONCATENATE function. In order to separate each argument, we can use an & symbol like the below image.

Combine Text with Dates 2

Note: Wherever we have typed a comma (,) we can replace it with & operator.

Things to Remember about Concatenate Date in Excel

  • We can select only one cell as the argument; we cannot select the rage of cells in concatenate function.
  • Using the TEXT function, we can modify the numbers to Date, Time, or whatever the format we want.
  • Ampersand is the alternative way we can combine instead of concatenating function.

Recommended Articles

This has been a guide to the Concatenate Date in Excel. Here we discuss how to use Concatenate keeping the date format in excel (using TEXT Function) along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Comments

  1. rajesh says

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

Cookies help us provide, protect and improve our products and services. By using our website, you agree to our use of cookies (Privacy Policy)