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

Get FREE Advanced Excel Exercises with Solutions!

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.

dataset to concatenate date month

  • You can use the DATE function to do that. Apply the following formula in cell E5 and drag the Fill Handle icon below.
=DATE(D5,C5,B5)

concatenate date month year using DATE function

Note: If you only have the months and years, use 1 as the date (day) argument. You will get #VALUE! error if the months are in plain text i.e January, February, etc. In that case, follow the other methods 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.

=C5&"/"&B5&"/"&D5

concatenate date month year using ampersand (&)

  • 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.
=C5&" "&B5&", "&D5

months in plain text

  • 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.
=E5+0

convert date_text to date by adding zero

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

convert date_text to date using DATEVALUE function

Note: You can convert texts to actual dates only if the texts have proper date formatting. For example, you can’t convert 12 10 2022 to actual dates as this is not a proper date format. But if the text looks like 12-10-2022 then you can convert it to actual dates.

Read More: How to Combine Date and Text in Excel (5 Ways)


Similar Readings


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.

=CONCAT(C5,"/",B5,"/",D5)

concatenate date month year using CONCAT function

Note: You can also use the CONCATENATE Function which is the earlier version of the CONCAT Function.

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.

=TEXTJOIN("/",TRUE,C5,B5,D5)

concatenate date month year using TEXTJOIN function

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.
=TEXT(B5,"yy/dd/mm")&" : "&C5&" "&D5

concatenate date month year using TEXT function

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


Similar Readings


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.

dataset to concatenate date and time

  • 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.
=B5+C5

concatenate date and time by adding them

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

format cells to change datevalue to date

  • Finally, the result will look as follows.

concatenate date and time

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.

dataset to concatenate multiple dates

  • 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.
=TEXT(B5,"m/dd/yyyy")&" - "&TEXT(C5,"m/dd/yyyy")

concatenate multiple dates

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.


Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo