Excel VBA DateSerial
DateSerial function in Excel VBA returns the date from the supplied values. We need to supply what is the year, what is the day, and what is the month.
Below is the syntax of DATESERIAL function in VBA.
#1 – Year: we need to enter the integer number as what is the year? While supplying integer number 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.
- 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 an 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 Excel VBA?
Below are the examples of DATESERIAL function in Excel VBA.
To use this excel VBA 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 DATESERIAL function in VBA.
For YEAR supply 2019, for MONTH supply 08, and for DAY supply 05.
Now show the result of the variable “My date” in the message box.
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 the date.
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.
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.
We can also declare VBA variables and supply the values to those variables. For an 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 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 DATESERIAL function.
This is how we can make use of variables in VBA to store values.
Now we will experiment with the year I will assign the year value as 1 and see the result.
Single & Double-digit numbers in YEAR represents 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.
Look at the code here, Year is 2019 and month is 0. But look at the result it says 05-Dec-2019, whereas a supplied year is 2019 it says 2018 i.e. 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 Syntax explanation carefully to understand.
- The result will be based on the date format of your system, if you want modified result then you need to apply FORMAT function mention the date format as per your convenience.
This has been a guide to VBA DateSerial. Here we learn how to use DATESERIAL Function in Excel VBA along with examples and download template. Below are some useful excel articles related to VBA –