VBA DateSerial

Excel VBA DateSerial Function

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

Below is the syntax of the DATESERIAL function.

DateSerial Syntax

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

  • We can supply numbers from 0 to 9999.
  • One & Two-digit numbers from 0 to 99 will be treated from 1930 to 2029.
  • The negative integer number will be subtracted from the year 2000. For example, if you supply -1, then the result will be 1999 because of 2000 – 1 = 1999.

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

  • We can supply numbers from 1 to 12 only.
  • If the supplied integer value is 0, then this will represent the month “December” of the previous year.
  • If the supplied integer value is -1, then 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, then it will be treated as the month “February” of the following year.

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

  • We can enter integer numbers from 1 to 31 for 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, then 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, then it will represent the first day of September.

How to use DATESERIAL Function in VBA?

You can download this VBA DateSerial Excel Template here – VBA DateSerial Excel Template

Example #1

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

Step 1: Start Sub Procedure

First, create a VBA subprocedure, as shown below.

VBA DateSerial Example 1
Step 2: Declare Variable

Now declare a variable as DATE.

VBA DateSerial Example 1-1
Step 3: Assign DateSerial Function to that variable.

Now for this variable, assign the DATESERIAL function.

VBA DateSerial Example 1-2
Step 4: Now enter the year, month, and day values in DateSerial function

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

VBA DateSerial Example 1-3
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
VBA DateSerial Example 1-4
Step 6: Run the code

Now run this code to see the below the date.

Example 1-5

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

We can also change the format of the date 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

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

Example 1-6

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 supplying year, month, and day directly to the DATESERIAL function, we have declared variables and assigned values to them. Then later, we have supplied variables to the DATESERIAL function.

This is how we can make use of variables in VBA to store values.

Example #3

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

Example 3

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

Now let’s change the month number to 0 and see the result.

Example 3-1

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

This is because since we have supplied the month as 0 DATESERIAL function takes the month to last month of the previous year and change the year also accordingly.

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

Things to Remember

  • You need to know what number represents which year, month, and daycare. Read the Syntax explanation carefully to understand.
  • The result will be based on the date format of your system. If you want a modified result, then you need to apply the FORMAT function mention the date format as per your convenience.

Recommended Articles

This has been a guide to VBA DateSerial. Here we learn how to use the VBA DATESERIAL function along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>