Combine day, month and year to create date

This tutorial shows how to combine the day, month and year values that are separately captured to derive with a date through the use of an Excel formula, with the DATE function or VBA

Excel

Combine date, month and year to create date using the DATE function

VBA

Combine date, month and year to create date using VBA

EXCEL FORMULA 1. Combine date, month and year to create date using the DATE function

EXCEL

Hard coded formula

Combine day, month and year to create date

Cell reference formula

=DATE(2019,3,17)

=DATE(D5,C5,B5)

GENERIC FORMULA

=DATE(year,month,day)

ARGUMENTS
year: The number that represents a year. This needs to be between two and four digits.
month: The number that represents a month.
day: The number that represents a day.

GENERIC FORMULA

=DATE(year,month,day)

ARGUMENTS
year: The number that represents a year. This needs to be between two and four digits.
month: The number that represents a month.
day: The number that represents a day.

EXPLANATION

This formula uses the DATE function to combine the day, month and year values to derive with a date.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the day, month and year numbers entered directly in the formula or referenced to cells.

In this example we are combining day 17 with the third month and year 2019 to derive with a date.

VBA CODE 1. Combine date, month and year to create date using VBA

VBA

Hard coded against single cell

Sub Combine_day_month_and_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine day, month and year to create with a date
ws.Range("B5") = DateSerial(2019, 3, 17)

End Sub

Cell reference against single cell

Sub Combine_day_month_and_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine day, month and year to create with a date
ws.Range("E5") = DateSerial(ws.Range("D5"), ws.Range("C5"), ws.Range("B5"))

End Sub

Hard coded against range of cells

Sub Combine_day_month_and_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine day, month and year to create with a date

For x = 5 To 8

ws.Range("B" & x) = DateSerial(2019, 3, 17)
Next x

End Sub

Cell reference against range of cells

Sub Combine_day_month_and_year()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'combine day, month and year to create with a date

For x = 5 To 8

ws.Range("E" & x) = DateSerial(ws.Range("D" & x), ws.Range("C" & x), ws.Range("B" & x))
Next x

End Sub

Related Topic	Description	Related Topic and Description
Combine date and time	How to combine a date and time in one cell through the use of an Excel formula or VBA
Difference in months between two dates	How to find the difference in months between two dates through the use of an Excel formula or VBA
Return last day of a current month	How to return the last day of a current month through the use of an Excel formula or VBA