VBA DateSerial

The DateSerial function in VBA returns the date from the values supplied by the users. So, we need to supply what is the year, what is the day, and what is the month. Then, based on your system's date format, the result will be. 

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.

Learning VBA allows users to automate tasks and create custom functions in applications like Microsoft Excel and Word. Those looking to enhance their data management skills through VBA can explore this Microsoft Office VBA Advanced Course.

Example #1

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

Step 1: Start Sub Procedure

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

Step 2: Declare Variable

Now, declare a variable as Date.

Step 3: Assign DateSerial Function to that variable.

Now for this variable, assign the DateSerial function.

Step 4: Now enter the year, month, and day values in DateSerial function

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

Step 5: Show Result in Message Box

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

Step 6: Run the code

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.

Code:

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.

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.

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