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

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

Steps:

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

• 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 Ampersand (&) Symbol

Steps:

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

• 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`

• 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`

• You can also use the DATEVALUE function to get the same result.
• Enter the following formula in cell F5:
`=DATEVALUE(E5)`

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)`

• 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 â€“ Employing the TEXTJOIN Function

Steps:

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

### Method 5 â€“ Concatenating Full Date to Month and Year

Steps:

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

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

• 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`

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

• 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, 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")`

## 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 separate the date, month, and year.

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF