What is VBA DateSerial?
VBA DATESERIAL function is a built-in function in Excel. DATESERIAL function returns a date given a year, month, and day value. Let’s say for example if today’s date is the 18th of July 2019. Some users may write it as 18-Jul’19 or 18-Jul-19. Formats for dates can be different for multiple users. This gives us the tedious task of work with a larger number of dates in different formats.
As the problem statement defined above, the format for dates can be different for multiple users. This is where date serial function comes in VBA to help. It is a very good function to make our own date with the values we choose to define. Let us know about this function in detail. DateSerial function in VBA is a date and time function so the returned value for this function is DATE. The method of using the syntax for this function will be as follows.
Syntax of VBA DateSerial
The syntax for VBA DateSerial function in excel is as follows:
It takes three arguments year, month, and day.
- Year: The accepted range for this part of the argument in the function is from 100-9999. The data type for the argument should be an integer.
- Month: The accepted range for this part of the argument in the function is from 1-12. The data type for the argument should be an integer.
- Day: The accepted range for this part of the argument in the function is from 1-31. The data type for the argument should be an integer.
But here is a cool part of this function. If we provide the argument above the range accepted, the function does not return an error instead, it calculates the further part of the range and displays the date. For example, if I give input as date serial ( 2019, 14, 02), now we know that there can be only 12 months in a year but I have provided 14 as an argument. The function will not return an error instead it will add the two extra months in the current year so the output will be 2020-02-02.
How to Use VBA DateSerial Function in Excel?
We will learn how to use a VBA DateSerial Function with a few examples in excel.
Let us test this function by a few examples which will make things much clearer for us.
Example #1 – VBA DateSerial
Let us first provide a simple date to a variable using the date serial function and see the output we get.
Step 1: Before we begin with our examples, first make sure to have developer’s tab enabled and then move to the code section to open the VB Editor by clicking on Visual Basic just like shown in the image below.
Step 2: From the window which is shown above click on the Insert tab to insert a module. We will work in the same module for all of our examples.
Step 3: Initiate a subprocedure as follows and declare a variable as date data type.
Code:
Sub Sample() Dim Dt As Date End Sub
Step 4: Now let us provide a date to the variable using the date serial function.
Code:
Sub Sample() Dim Dt As Date Dt = DateSerial(2019, 7, 2) End Sub
Step 5: Display the value stored in our variable using the msgbox function.
Code:
Sub Sample() Dim Dt As Date Dt = DateSerial(2019, 7, 2) MsgBox Dt End Sub
Step 6: When we execute the above code we get the following result.
Example #2 – VBA DateSerial
Now let us test the theory we discussed above in the introduction that if we provide the values apart from the accepted range and the function calculates the date for us.
Step 1: In the same module start another subprocedure as follows.
Code:
Sub Sample1() End Sub
Step 2: Now define a variable with date data type.
Code:
Sub Sample1() Dim Dt As Date End Sub
Step 3: Let us provide the input to the DT variable using the date serial function as shown below.
Code:
Sub Sample1() Dim Dt As Date Dt = DateSerial(2019, 14, 2) End Sub
Step 4: Display the value stored in Dt variable using the msgbox function.
Code:
Sub Sample1() Dim Dt As Date Dt = DateSerial(2019, 14, 2) MsgBox Dt End Sub
Step 5: Now execute the above code and see the result below as follows.
We can see that the year has been changed as we had provided two extra months to the arguments. Basically, this function added 14 months to the year 2019 which gave us the second month of 2020 and 2nd Date.
Example #3 – VBA DateSerial
In the above examples, we provided the arguments in the general date fashion. But we discussed how users can input absurd values in the date section as a format, for writing dates is different from individual to individual. We will compare two date formats using the date serial function in this example.
Step 1: In the same module define another subprocedure as follows for the third example.
Code:
Sub Sample2() End Sub
Step 2: In this scenario define two variables as dates as shown below.
Code:
Sub Sample2() Dim Dt1, Dt2 As Date End Sub
Step 3: Now in the First variable let us provide date in simple regular fashion using the dateserial function.
Code:
Sub Sample2() Dim Dt1, Dt2 As Date Dt1 = DateSerial(2019, 12, 31) End Sub
Step 4: Now for the second variable let us provide date in irregular fashion using the dateserial function as shown below.
Code:
Sub Sample2() Dim Dt1, Dt2 As Date Dt1 = DateSerial(2019, 12, 31) Dt2 = DateSerial(19, 12, 31) End Sub
Step 5: Now print both these values using the msgbox function.
Code:
Sub Sample2() Dim Dt1, Dt2 As Date Dt1 = DateSerial(2019, 12, 31) Dt2 = DateSerial(19, 12, 31) MsgBox Dt1 & " " & Dt2 End Sub
Step 6: Execute the above code to find the following result shown below.
We can see that both the dates are same. The two-digit year 19 is interpreted as 2019.
Things to Remember
- It is a date and time function so the output is in Date.
- If the value provided to the function is above the accepted range of the function it automatically calculates the date rather than giving an error.
- If the values provided to the arguments is greater than the value an integer can hold, then the function returns an error.
Recommended Articles
This is a guide to VBA DateSerial. Here we discuss how to use Excel VBA DateSerial Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
