How to concatenate year, month and day to date in Excel?
When working with different datasets in Excel, you might encounter situations where the month, day, and year for a date are stored in separate cells, such as A2 for the month, B2 for the day, and C2 for the year. Combining these elements into a true date format in a single cell not only makes your data easier to read, but also enables you to perform date calculations, sorting, and formatting more conveniently. Below is an example showing the separate values and their concatenation into a date:
In this tutorial, several methods are introduced to concatenate the year, month, and day into an Excel date, accommodating different needs for calculation and presentation. You can choose a suitable approach depending on whether you want a simple text result, a true date value, or an automated batch solution.
Concatenate year, month and day to date with formula
Concatenate year, month and day to date with an amazing tool
Excel Formula - Use DATE function to create a true Excel date value
VBA Code - Automate concatenation of year, month, and day values into Excel date format across multiple rows
Concatenate year, month and day to date with formula
If your primary aim is to display the combined date as a text string in a single cell, you can use a basic concatenation formula. This is a quick method to visually present the full date, though the result will remain as text and not a real Excel date value. This means you won't be able to perform date calculations or utilize automatic date formatting on the result.
1. Select a blank cell where you want the concatenated date to appear, and enter the following formula into the formula bar:
=A2&"/"&B2&"/"&C2After entering the formula, press Enter to confirm your input.
2. To apply this formula to additional rows, drag the Fill Handle from the populated cell down to the other cells in the column. This will concatenate month, day, and year for each row in your selection.
Note: In this formula, the "/" character serves as the separator between the month, day, and year. You can adjust the delimiter to match your local date format or project needs, such as using a dash "-" or period ".".
Through this method, the separated month, day, and year values are efficiently combined. However, since the result is a text string, further date operations such as sorting or arithmetic will require conversion to the true date format.
Concatenate year, month and day to date with an amazing tool
The Combine feature within Kutools for Excel provides an intuitive solution for merging values from multiple cells into a single one, streamlining the process without requiring complex formulas. This approach is especially beneficial if you need to perform the task repeatedly or on large data ranges, as Kutools enables batch combining with customizable separators and output options.
To utilize this function, ensure your month, day, and year data are located in adjacent columns. Then, follow these steps:
1. Select the relevant columns containing the month, day, and year, and click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data.
2. In the pop-up Combine Columns or Rows dialog box, configure the following settings:
After completing these steps, your selected cells are combined into one cell as a date string. Kutools offers a simple and reliable way to process large datasets and supports multiple customizations depending on your requirements. Be mindful of local date format differences when setting delimiters.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Excel Formula - Use DATE function to create a true Excel date value
For situations where you not only want to display the date but also perform further calculations, apply automatic formatting, or leverage Excel's date functions, combining the values into a true date format is recommended. The DATE function in Excel enables this. By merging year, month, and day into a serial date number, operations like calculating durations, scheduling, and sorting by date are instantly possible.
Applicable scenario: Use when you require the result to be a real Excel date rather than a text string, especially for financial reports, time tracking, or any task needing chronological analysis.
Caution: Ensure that each cell contains valid values (e.g., months between 1-12, days within the month's range, and a valid year) to prevent formula errors.
1. Click on a blank cell where you want the true date to appear (for example, D2), and enter the following formula:
=DATE(C2,A2,B2)In this formula, C2 should contain the year, A2 the month, and B2 the day. Adjust the cell references as needed for your worksheet structure.
2. Press Enter to confirm the formula. The resulting cell now contains a true date value, which you can format to display as "mm/dd/yyyy" or any other date format using Home > Number Format options.
3. To apply the formula to additional rows, select the result cell, use the Fill Handle to drag down, and populate the formula across your dataset.
Tip: If you notice a serial number instead of a regular date, change the cell format by selecting the cell(s), right-clicking, and choosing Format Cells > Date. This will display the value in standard date notation.
Precautions: Watch for errors such as #VALUE! if any of the referenced cells contain non-numeric text. Check for correct order of year, month, and day to match your data.
VBA Code - Automate concatenation of year, month, and day values into Excel date format across multiple rows
For advanced batch processing or automation—especially when dealing with large lists or wanting to eliminate manual entry—using VBA is practical. A simple macro can concatenate values in specified columns to generate true Excel dates throughout your dataset. This approach offers flexibility and efficiency, enabling you to handle hundreds or thousands of rows with a single click.
Applicable scenario: Choose VBA automation when you have repeated, large-scale tasks requiring true date values in a specific column, or want additional customization.
1. Access the VBA editor in Excel by clicking Developer Tools > Visual Basic. When the Microsoft Visual Basic for Applications window appears, click Insert > Module.
2. Copy and paste the following code into the new module:
Sub ConcatDateToTrueDate_Fixed()
'Updated by Extendoffice 20250903
Dim ws As Worksheet
Dim StartCell As Range
Dim i As Long
Dim lastRow As Long
Dim yearCol As String
Dim monthCol As String
Dim dayCol As String
Dim outCol As String
Dim xTitleId As String
xTitleId = "Date Conversion"
Set ws = ActiveSheet
On Error Resume Next
Set StartCell = Application.InputBox("Select the FIRST DATA CELL in your table (e.g., A2). The macro will process all rows below it.", xTitleId, ws.Range("A2").Address, Type:=8)
On Error GoTo 0
If StartCell Is Nothing Then Exit Sub
yearCol = UCase(Trim(Application.InputBox("Enter the LETTER of the YEAR column (e.g., C):", xTitleId, "C", Type:=2)))
monthCol = UCase(Trim(Application.InputBox("Enter the LETTER of the MONTH column (e.g., A):", xTitleId, "A", Type:=2)))
dayCol = UCase(Trim(Application.InputBox("Enter the LETTER of the DAY column (e.g., B):", xTitleId, "B", Type:=2)))
outCol = UCase(Trim(Application.InputBox("Enter the LETTER of the OUTPUT column (e.g., D):", xTitleId, "D", Type:=2)))
If Len(yearCol) <> 1 Or Len(monthCol) <> 1 Or Len(dayCol) <> 1 Or Len(outCol) <> 1 Then
MsgBox "Invalid column letter entered. Please enter a single letter (e.g., A, B, C).", vbExclamation, xTitleId
Exit Sub
End If
lastRow = Application.Max(ws.Cells(ws.Rows.Count, yearCol).End(xlUp).Row, _
ws.Cells(ws.Rows.Count, monthCol).End(xlUp).Row, _
ws.Cells(ws.Rows.Count, dayCol).End(xlUp).Row)
If StartCell.Row > lastRow Then
MsgBox "No data found to process.", vbInformation, xTitleId
Exit Sub
End If
Application.ScreenUpdating = False
For i = StartCell.Row To lastRow
If IsNumeric(ws.Cells(i, yearCol).Value) And _
IsNumeric(ws.Cells(i, monthCol).Value) And _
IsNumeric(ws.Cells(i, dayCol).Value) Then
On Error Resume Next
ws.Cells(i, outCol).Value = DateSerial( _
Year:=ws.Cells(i, yearCol).Value, _
Month:=ws.Cells(i, monthCol).Value, _
Day:=ws.Cells(i, dayCol).Value)
If Err.Number <> 0 Then
ws.Cells(i, outCol).Value = "Invalid Date"
Err.Clear
End If
On Error GoTo 0
Else
ws.Cells(i, outCol).Value = ""
End If
Next i
Application.ScreenUpdating = True
MsgBox "Date conversion completed successfully! Processed rows: " & (lastRow - StartCell.Row + 1), vbInformation, xTitleId
End Sub3. To run the code, click the 
button. A prompt will guide you to select a starting cell in your dataset, specify the columns for year, month, day, and the output for the resulting date.
Upon completion, the macro fills the specified output column throughout your data with real Excel date values, ready for calculation or formatting. Should you encounter errors, confirm your column references and data integrity before running again.
Practical tips: It's best to save your work before executing macro scripts. For repeated use, consider saving the code as a custom macro in your workbook.
When choosing among these methods, consider your needs for calculation, automation, and ease of use. For simple display, direct concatenation works well and is fast, while the DATE function and VBA solutions provide genuine date values suitable for deeper analysis and operations. Kutools offers efficient batch operations and flexibility for varied data formats.
If errors arise (such as unexpected data formats, invalid values, or formula failures), double-check your source cells and references. Use error handling options in formulas or VBA to prompt corrections. Changing cell formats can resolve most display issues for true dates.
Related articles:
- How to combine cells and keep the cell formatting in Excel?
- How to concatenate cells if same value exists in another column in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in