The VBA DateSerial Function

Related Function:
VBA TimeSerial

Description

The VBA DateSerial Function returns a Date from a supplied year, month and day number.

The syntax of the function is:

DateSerial( Year, Month, Day )

Where the function arguments are:

Year-

An integer representing the year.

Note that:

  • One- and Two-digit year numbers (from 0 to 99) are interpreted as years between 1930 and 2029;
  • Negative year numbers are subtracted from the year 2000 (e.g. -1 represents the year 1999, etc.).
Month-

An integer representing the month.

Integer values less than 1 or greater than 12 are interpreted as follows:

-1 - represents November of the previous year
0 - represents December of the previous year
13 - represents January of the following year
14 - represents February of the following year
etc.
Day-

An integer representing the day of the month.

Integer values less than 1 or greater than the number of days in the current month are interpreted as follows:

-1 - represents the second to the last day of the previous month
0 - represents the last day of the previous month
days in current
month + 1
- represents the first day of the following month
days in current
month + 2
- represents the second day of the following month
etc.


VBA DateSerial Function Examples

Example 1 - Simple Use of the DateSerial Function

' Two different ways to return the date "12/31/2015"
Dim dt1 As Date
Dim dt2 As Date
dt1 = DateSerial( 2015, 12, 31 )
dt2 = DateSerial( 15, 12, 31 )
' The variables dt1 and dt1 now both are equal to the Date 12/31/2015.

After running the above VBA code, the variables dt1 and dt2 are both equal to the date 12/31/2015.

Note that the two-digit Year number 15 has been interpreted as the year 2015.


Example 2 - Using Month Numbers That Are Less Than 1 Or Greater Than 12

The following VBA code shows how month numbers that are less than 1 or greater than 12 are interpreted as months in the previous or following year.

' Return Four Different Dates
Dim dt1 As Date
Dim dt2 As Date
Dim dt3 As Date
Dim dt4 As Date
dt1 = DateSerial( 2015, 0, 31 )
' dt1 is now equal to the Date 12/31/2014.
dt2 = DateSerial( 2015, 1, 31 )
' dt2 is now equal to the Date 1/31/2015.
dt3 = DateSerial( 2015, 12, 31 )
' dt3 is now equal to the Date 12/31/2015.
dt4 = DateSerial( 2015, 13, 31 )
' dt4 is now equal to the Date 1/31/2016.

After running the above VBA code, dt1 = 12/31/2014, dt2 = 1/31/2015, dt3 = 12/31/2015 and dt4 = 1/31/2016.


Example 3 - Using Day Numbers That Are Less Than 1 Or Greater Than 31

The following VBA code shows how day numbers that are less than 1 or greater than number of days in the current month are interpreted as days in the previous or following month.

' Return Four Different Dates
Dim dt1 As Date
Dim dt2 As Date
Dim dt3 As Date
Dim dt4 As Date
dt1 = DateSerial( 2015, 12, 0 )
' dt1 is now equal to the Date 11/30/2015.
dt2 = DateSerial( 2015, 12, 1 )
' dt2 is now equal to the Date 12/1/2015.
dt3 = DateSerial( 2015, 12, 31 )
' dt3 is now equal to the Date 12/31/2015.
dt4 = DateSerial( 2015, 12, 32 )
' dt4 is now equal to the Date 1/1/2016.

After running the above VBA code, dt1 = 11/30/2015, dt2 = 12/1/2015, dt3 = 12/31/2015 and dt4 = 1/1/2016.