WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Concatenate Date in Excel

Concatenate Date in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj 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 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 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.

Step 1: Open the concatenate formula.

Using Concatenate Function step 2

Step 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

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Using Concatenate Function step 4

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

Using Concatenate Function step 5

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

Using Concatenate Function step 6

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

Using Concatenate Function step 7

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

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

Using Concatenate Function step 9

Step 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

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

Using Concatenate Function step 11

Step 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 cells 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 –

  • Concatenate Columns in Excel
  • Insert Date in Excel
  • How to use the DATE Excel Function?
  • Calculate Age in Excel
6 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Concatenate Date Excel Template

Coursera IPO Financial Model & Valuation Free Download