Table of Contents
- Merging Dates with Concatenate Function in Excel
- #1 – Combine Text with Dates Using Concatenate Function
- #2 – Concatenate Text with Dates Using Alternative Method
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 thinking what is 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 use concatenate function. 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 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: 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.
Step 4: Third argument is date, so select date cell.
Step 5: the Fourth argument is also not there in the data. Type “for “.
Step 6: Final argument is department and select department cell.
Step 7: we have the full sentence is ready.
Oh oh, hang on notice the date part here. We don’t have the accurate date here, after using concatenate in excel, formula thinks date as the number, not the date.
We need to make the number to a date format using TEXT function in excel.
Step 8: Edit the formula, in the third argument apply TEXT function.
Step 9: TEXT function’s first argument is VALUE. It is asking which value to be formatted, so select date cell here.
Step 10: Final part of the TEXT function is FORMAT TEXT 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 TEXT function to format date, the time when we are combining. TEXT function allows us to modify the format of the cell based on our requirement.
#2 – Concatenate Text with Dates Using Alternative Method
We have learned 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 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.
You can download this Concatenate Date Excel template here – Concatenate Date Excel Template
Recommended Articles
This has been a guide to 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 –
Leave a Reply