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 function. 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 Concatenate. Assume you are working as a recruiter in a company, and you have recruited 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 for Sales Department.
Yes, you need to use the CONCATENATE function to frame a sentence like this.
Step 1: Open the concatenate formula.
Step 2: First value we need to show here is the emp name, so select the emp name as the first argument.
Step 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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Step 4: The third argument is a date, so select the date cell.
Step 5: the Fourth argument is also not there in the data. Type “for. “
Step 6: Final argument is the department and select department cell.
Step 7: we have the full sentence ready.
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.
Step 8: Edit the formula; in the third argument, apply the TEXT function.
Step 9: TEXT function’s first argument is VALUE. It is asking which value to be formatted, so select the date cell here.
Step 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 date, mention the date format as “DD-MMM-YYYY.”
Step 11: Now, hit the enter button. We must have got accurate date value.
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 cells into one.
We need to remove the CONCATENATE function. In order to separate each argument, we can use an & symbol like the below image.
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 –
- Concatenate Columns in Excel
- Insert Date in Excel
- How to use the DATE Excel Function?
- Calculate Age in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
