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

Posted on by Md. Shamim Reza

Method 1 – Using the DATE Function

The below dataset has days, months, and years in separate columns. We need to concatenate them to create proper dates.

dataset to concatenate date month

Steps:

  • Enter the following formula in cell E5:
=DATE(D5,C5,B5)

concatenate date month year using DATE function

  • Drag the Fill Handle icon below.
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.

Method 2 – Utilizing the Ampersand (&) Symbol

Steps:

  • Enter the following formula in cell D5:
=C5&"/"&B5&"/"&D5

concatenate date month year using ampersand (&)

  • The months are in plain text, i.e., January, February, etc.
  • Use proper delimiters (hyphens, commas, spaces, etc) within the double quotes to get more appropriate results.
  • Enter the following formula in cell D5:
=C5&" "&B5&", "&D5

months in plain text

  • The results obtained using both of the formulas above are not actual dates but rather texts. You need to convert them to dates to apply the desired Date Format. The two simple ways to do that are by adding a zero to them and by using the DATEVALUE function.
  • Enter the following formula in cell F5:
=E5+0

convert date_text to date by adding zero

  • You can also use the DATEVALUE function to get the same result.
  • Enter the following formula in cell F5:
=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

Method 3 – Applying the CONCAT Function

Steps:

  • Enter the following formula in cell E:
=CONCAT(C5,"/",B5,"/",D5)

concatenate date month year using CONCAT function

  • drag the Fill Handle icon below.
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

Method 4 – Using the TEXTJOIN Function

Steps:

  • Enter the following formula in cell E5:
=TEXTJOIN("/",TRUE,C5,B5,D5)

concatenate date month year using TEXTJOIN function

Method 5 – Concatenating the Full Date to Month and Year

Steps:

  • Enter the following formula in cell E5:
=TEXT(B5,"yy/dd/mm")&" : "&C5&" "&D5

concatenate date month year using TEXT function

  • 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. 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.
  • Enter the following formula in cell D5:
=B5+C5

concatenate date and time by adding them

  • Select the concatenated values and enter the desired formatting from Home >> Format >> Format Cells.

format cells to change datevalue to date

  • 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, you must also use the TEXT Function to keep the formatting.
  • Enter 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. If you don’t enter them in the correct format, they will be text values. Remember to enter the dates according to the system Date Format.
  • Always put double quotes around the delimiters that separate the date, month, and year.

Download the Practice Workbook

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF