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.


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.

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

=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: 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.

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

concatenate date month year using TEXTJOIN function


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


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


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


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.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo