How to Concatenate Date in Excel?

To concatenate date in Excel with other values, we can use an operator or also known as concatenate operator 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.

In this article, we 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 what the big deal about combining dates when you already know how to connect two or more cell string values is.

Yes, it is different when you merge dates in Excel because it would not give you the exact result you want. Ok, let us go ahead and learn how to combine 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 strings. Assume you are working as a recruiter in a company. You have recruited the below employees in the current month.

You have their employee ID, name, salary, joining date, and their respective department.

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

Raju joined the company on 25-May-2017 in the sales department.

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

  • 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 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 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. We have a proper sentence. We need to 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.

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.

We have a proper sentence. We need to 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.

“&” (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.

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

Note: Wherever we type 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 choose the range of cells in the CONCATENATE function.Using the TEXT function, we can modify the numbers to date, time, or the format we want.Ampersand is the alternative way we can combine instead of concatenating functions.

This article has been a guide to the Concatenate Date in Excel. We discuss using concatenate keeping the date format in Excel (TEXT function) and practical examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Concatenate Columns in ExcelInsert Date in ExcelHow to use the DATE Excel Function?Calculate Age in Excel