How to Concatenate Date/Day, Month, and Year in Excel

Frequently, we combine day, month, and year to create a complete date in Excel. But sometimes, you might need to combine the date along with the month and year for your specific purpose. Whatever, we’ll discuss 5 diverse methods to cover these two tasks with proper explanation. Hopefully, these methods would be handy for you.

5 Methods to Concatenate Date/Day, Month, and Year in Excel

Here we will explain 5 different ways to concatenate date (day), month, and year in excel. Let’s start!

1. Using DATE Function

Assume you have the days, months, and years in separate columns as follows. Now you need to concatenate them to create proper dates.

• You can use the DATE function to do that. Apply the following formula in cell E5 and drag the Fill Handle icon below.

2. Utilizing Ampersand (&) Symbol

You can use the Ampersand (&) symbol to get the same result. Apply the following formula in cell D5 and then copy it down.

• Suppose the months are in plain text i.e January, February, etc. Then use proper delimiters (hyphen, comma, space, etc) within the double quotes to get more appropriate results. For example, apply the following formula in cell D5 and copy the formula below.

• The result obtained using both of the formulas above are not actual dates but rather texts. You need to convert them to dates to be able to apply desired Date Format. The two simple ways to do that are by adding a zero to them and by using the DATEVALUE function.
• Now enter the following formula in cell F5 and copy the formula below. This will convert the texts to date values. Then apply any desired Date Format you like.

• Alternatively, you can use the DATEVALUE function to get the same result. Apply the following formula in cell F5 and copy it below.

Read More: Excel VBA: Combine Date and Time

3. Applying CONCAT Function

You can also use the CONCAT function to concatenate dates, months, and years. Apply the following formula in cell E5 and drag the Fill Handle icon below.

Read More: How to Combine Name and Date in Excel

4. Employing TEXTJOIN Function

The TEXTJOIN Function will also allow you to get the same result. Apply the following formula in cell E5 and copy the formula down.

5. Concatenating Full Date to Month and Year

Assume you have full dates, months, and years in separate columns. The formatting of the dates is a little confusing. So you want to concatenate them with the months and years. But if you try using any of the earlier methods, it will concatenate the dates as numeric values unless the dates are in text format.

• So what do you do? Well, you can you the TEXT Function to concatenate them and keep the same formatting. Apply the following formula in cell E5 and drag the Fill Handle icon to see the following results.

How to Concatenate Date and Time in Excel

Suppose you have dates and times in separate columns as shown below. Now you need to concatenate them into a single column.

• If the values are actual date and time values or properly formatted texts, you can use the addition (+) operator to concatenate them in a single column. Apply the following formula in cell D5 and copy it below.

• Then select the concatenated values and apply the desired formatting from Home >> Format >> Format Cells.

• Finally, the result will look as follows.

How to Concatenate Multiple Dates in Excel

Suppose you have the Start Date and End Date in separate columns as follows. You need to concatenate them in a single column as Start Date – End Date.

• If the dates are in text format, you can simply use the Ampersand (&) symbol to do that. But if these are date values, then you need to use the TEXT Function also to keep the formatting.
• Apply the following formula in cell D5 and drag the Fill Handle icon below.

Things to Remember

• Dates in Excel are sensitive to system date settings. So remember to enter the dates according to the system Date Format.
• Always put double quotes around the delimiters that will separate the date, month, and year.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to concatenate date, month, and year in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.