How To Concatenate A Date

Excel Concatenate Appointment (Table of Contents)

Introduction to Concatenate Date in Excel
How to Concatenate Date in Excel?

Introduction to Concatenate Date in Excel

To Concatenate Dates in Excel, first, we need to convert the cells which contain Date into Text format using the TEXT office, and in that location we will choose the format of the appointment that we want to go along in text format. Insert CONCATENATE function in a prison cell and right afterward starting the TEXT function. Select the cell that contains the date and choose the engagement we want to go along in inverted commas. Then subsequently choosing the cell with which nosotros want to concatenate. We can use blank infinite to separate the date and other cell values to distinguish the departure.

How to Concatenate Date in Excel?

There are various methods to Concatenate Dates in Excel, Concatenate Dates with Fourth dimension, Convert Concatenated Dates into desired formats. Let'due south understand How to do information technology with a few illustrations.

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

Illustration #i – Combining Year and Month Fields to Form a Date Field

Sometimes the data nosotros have has separate fields specifying "Calendar month" and "Twelvemonth", and we are required to create a complete date using these two fields. This tin be washed by using the CONCATENATE() function or the & operator in Excel.

Follow the below steps to understand the working of Concatenating Dates in Excel.

Let's consider that we have two fields in the dataset received from the client, namely "Yr" and Month, both in numeric format. Our aim is to create a third field with a date format.

Step i:Click on the prison cell where yous wish to create the new date and enter the formula beneath in the destination cell. You can use '-'or '/' as separators.

Step ii: By dragging the formula applied to cell C2 till jail cell C5, we will get the results for the remaining data.

Step three:You lot can also utilise the following alternative formula for the same result.

Stride iv:By dragging the formula practical to cell C2 till cell C5, we will get the results for the remaining data.

Even so, there is a small caveat in the above method, i.e. it does not allow the format of the date to be changed.

Analogy #two – Combining Year and Month/24-hour interval Fields to Form a Date Field in the desired Format

Changing the format of Concatenated Dates in Excel can be done through multiple methods :

Method #1 – Adding the number 0 to the existing formula

Step 1: Click on the cell where y'all want to create the new date and enter the formula below in the selected cell.

Step 2:Drag the formula applied to cell C2 till cell C5, so we volition get the results for the remaining data.

Pace 3: Insert 0 to the value of the new field created.

Step 4: Apply the aforementioned formula to the remaining cells. The result is shown in the below screenshot.

Pace 5: At present go to the Number section in the Dwelling house Tab and click on the dropdown next to General to select More Number Formats.

Pace 6: From the dialog box, select "Date" under "Category".

Step 7: Pick the desired date format from the "Type" section and click "OK"

And so we volition get the upshot as follows.

Method #2 – Using the Text() office in the formula

Step 1: Click on the cell where you desire to create the new appointment and enter the formula below in the selected cell to become a date in a specified format.

Step 2:Afterward applying the aforementioned formula to the remaining cells, nosotros will get the result as follows.

Notation:

By default, Excel uses the first mean solar day of the month in the date format that contains 24-hour interval also.
In instance yous concatenate mean solar day and calendar month fields to get a consummate date field, Excel will use the current year in the date past default.
The order of concatenation is essential for this scenario. If y'all concatenate the twelvemonth and month in the reverse order, i.e. Twelvemonth before a calendar month, Excel will not interpret it as a date, and yous will not be able to change the format.

Illustration #3 – Concatenate Year, Month, and Mean solar day Fields to Grade a Date Field

The data can as well have separate fields specifying "Twenty-four hours", "Month", and "Year", and we are required to create a consummate date field using all three.

To accomplish this goal, follow the steps below:

Step 1: Click on the cell where you wish to create the new date and enter the formula below in the selected cell.

Step 2: After applying the same formula to the remaining cells, nosotros will get the result every bit follows.

Illustration #iv – Concatenate Engagement Field with String Field

In Excel, sometimes, you lot will be required to concatenate engagement fields with string fields to create a unique primal for the whole dataset. This can exist achieved by post-obit the below steps:

Stride 1: Click on the jail cell where you wish to create the new concatenated field and enter the formula below in the selected jail cell.

Footstep 2: Apply the aforementioned formula to the remaining cells. The upshot is shown in the below screenshot.

Note:

While concatenating the engagement and cord, you must use the TEXT() function to specify the format of the date before you concatenate information technology with the string.
If non, Excel will concatenate its numeric value with the string.

Illustration #5 – Concatenate Date Field with Number Field

You may also be required to concatenate the date field in excel with a numeric field. This could exist catchy business, only you need to exist careful while concatenating the 2. Allow'due south see how to do it.

Pace 1: Click on the cell where you lot wish to create the new concatenated field and enter the formula beneath in the selected jail cell.

Pace 2: Apply the same formula to all the remaining cells. The effect is shown in the below screenshot.

Annotation:

While concatenating the engagement and numeric, you must use the TEXT() function to specify the format of the date earlier you concatenate it with the numeric.
If not, Excel will treat the numeric value as a part of the date itself, and the issue obtained will be erroneous.

Analogy #vi – Combine Two or More than Date Fields

In Excel, Concatenating more than than one appointment tin exist washed through multiple methods :

Method #1 – Using text office separately

For concatenating two dates, we need to ensure that we utilize text functions separately on both fields prior to concatenation, as shown below:

Pace one: Click on the prison cell where you wish to create the new concatenated field and enter the formula below in the selected cell.

Step two: Use the same formula to all the remaining cells. The result is shown in the below screenshot.

Method #2 – Using the CONCAT() Function

For concatenating more than two dates in Excel, we will use the CONCAT() function every bit it can take a range of cells as an input, as shown below:

Step 1: Click on a new jail cell where you lot need to store the dates in text format and use the Text function as given in the beneath formula.

Step two: Employ the same formula to the remaining cells and then we volition get the upshot as follows.

Step 3: Select the cell where you wish to create the new concatenated field and enter the formula below in the selected prison cell.

Analogy #seven – Concatenate Date and Time Fields

Concatenating Dates and Fourth dimension in Excel is like shooting fish in a barrel, and this tin can be done through multiple methods :

Method #ane

For concatenating the date and time field in excel, nosotros need to ensure that we utilize the text function separately on both fields prior to concatenation as shown in the below steps:

Click on the cell where you wish to create the new field and enter the formula below in the selected prison cell.

Method #ii

The method given above is followed every bit all-time exercise. However, there is an easier manner of implementing the required results, as shown beneath.

Click on the cell where you lot wish to create the new field and enter the formula beneath in the selected cell.

Things to Remember About Concatenate Appointment in Excel

It is very important to know how to utilize the TEXT() part in unlike scenarios because if information technology is non used, Excel will concatenate the numeric value of the appointment.
The order of concatenation of date in excel is very important. Excel will not translate it equally a date if you concatenate the year and month in lodge (Yr earlier Calendar month).
We should know when to use CONCAT() role and CONCATENATE() function to concatenate the date in excel, as the erstwhile tin can take a range while in the latter, nosotros have to enter the values one past one.