Excel VBA DateSerial Function

Below is the syntax of the DateSerial function.

#1 – Year as Integer: We need to enter the integer number as what the year is. While supplying integer numbers, we must keep the things below in mind.

  • We can supply numbers from 0 to 9999.It will treat one and two-digit numbers from 0 to 99 from 1930 to 2029.The negative integer number will subtract from the year 2000. So, for example, if you supply -1, the result will be 1999 because 2000 – 1 = 1999.

#2 – Month As Integer: We need to enter the integer number as what the month is. While entering this number, we must keep the things below in mind.

  • We can supply numbers from 1 to 12 only.If the supplied integer value is 0, this will represent the month “December” of the previous year.If the supplied integer value is -1, this will represent the month “November” of the previous year. Like this, when the negative value increases, it will keep representing the backward year month.If the supplied number is more than 12, i.e., if the number is 13, then this will represent the month “January” of the following year. If the number is 14, it will be the month “February” of the following year.

#3 – Day As Integer: We need to enter the integer number as what the day is. While entering this number, we must keep the things below in mind.

  • We can enter integer numbers from 1 to 31 for the current month days.If the number is 0, it will represent the last day of the previous month.If the number is -1, it will represent the second last day of the previous month.If you supply the last day of this month +1, this will represent the first day of the following month. For example, if the last day of August is 31 and if you supply the day as 31 + 1, it will represent the first day of September.

How to use DATESERIAL Function in VBA?

Example #1

To use the DateSerial function, start writing the macro code.

First, create a VBA sub procedure, as shown below.

Now, declare a variable as Date.

Now for this variable, assign the DateSerial function.

For YEAR supply 2019, MONTH supply 08, and DAY supply 05.

Now, show the result of the variable “My date” in the message box.

Code:

Option Explicit

Sub DateSerial_Example1()

Dim Mydate As Date

Mydate = DateSerial(2019, 8, 5)

MsgBox Mydate

End Sub

Now, run this code to see the below date.

The result says “8/5/2019”. In the system, the date format is in the form of “MM-DD-YYYY.” The result is also in the same format.

We can also change the date format by using the FORMAT function in VBA. Apply FORMAT function like the below.

Option Explicit

Sub DateSerial_Example1()

Dim Mydate As Date

Mydate = DateSerial(2019, 8, 5)

MsgBox Format(Mydate, “DD-MMM-YYYY”)

End Sub

It will apply the format in “DD-MMM-YYYY,” and the result is as follows.

Example #2

We can also declare variables and supply the values to those variables. For example, look at the below code.

Sub DateSerial_Example2()

Dim Mydate As Date

Dim MyYear As Integer Dim MyMonth As Integer Dim MyDay As Integer

MyYear = 2019 MyMonth = 8 MyDay = 5

Mydate = DateSerial(MyYear, MyMonth, MyDay) MsgBox Format(Mydate, “DD-MMM-YYYY”)

End Sub

Instead of directly supplying year, month, and day to the DATESERIAL function, we have declared variables and assigned values to them. Then later, we supplied variables to the DateSerial function.

Like this, we can use variables in VBA to store values.

Example #3

Now, we will experiment with the year. First, we will assign the year value as 1 and see the result.

Single and double-digit numbers in YEAR represent a year from 1930 to 2029. So 01 means 2001, 02 means 2002, and so on.

Let us change the month number to 0 and see the result.

Look at the code here, the year is 2019, and the month is 0. But look at the result, it says 05-Dec-2019, whereas a supplied year is 2019 and 2018, i.e., the previous year since we have supplied the month as 0.

So, the DateSerial function takes the month to the last month of the previous year and changes the year accordingly.

Like this, try different numbers to see the impact of the function.

Things to Remember

  • We must know what number represents which year, month, and daycare. Read the syntax explanation carefully to understand.Based on the date format of the system, it gives the result. If you want a modified result, then you need to apply the FORMAT function and mention the date format as per your convenience.

This article has been a guide to VBA DateSerial. Here, we learn how to use the VBA DateSerial function, examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • VBA Asc FunctionVBA DatePartVBA DateDiff FunctionVBA DateAddVBA Web Scraping