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.

Download Practice Workbook

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

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.

Read More: How to Concatenate Date That Doesn’t Become Number in Excel (5 Ways)

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: How to Combine Date and Text in Excel (5 Ways)

Similar Readings

• Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
• How to Concatenate String and Integer using VBA
• Concatenate Not Working in Excel (3 Reasons with Solutions)
• How to Combine Rows into One Cell in Excel
• Combine Text from Two or More Cells into One Cell in Excel (5 Methods)

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: Combine Multiple Columns into One Column 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.

Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)

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.

Read More: How to Concatenate Columns in Excel (8 Simple Methods)

Similar Readings

• How to Merge Rows in Excel (2 Easy Methods)
• Concatenate with Space in Excel (3 Suitable Ways)
• How to Concatenate Range in Excel (For Both Old and New Versions)
• Opposite of Concatenate in Excel (4 Options)
• How to Concatenate Two Columns In Excel (5 Methods)

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.

Read More: Combine Text in Excel (8 Suitable Ways)

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.

Read More: How to Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)

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.

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.