VBA DateSerial

Excel VBA DateSerial Function

The VBA DateSerial function returns a date based on the inputs provided by the users in the form of year, month, and day. The result will be according to your system’s date format.

For example, when we collect dates from multiple users, the date format could vary from one user to another. For instance, Mr. A can give the date format as July 14^th, 2023, while Mr. B can provide the same date as 14^th July, 2023. Therefore, we can use the Date Serial function to avoid ambiguity, especially with larger data sets.

The VBA Dateserial syntax is as follows.

DateSerial (Year as Integer, Month as Integer, Day as Integer)

The Date Serial function has three mandatory arguments.

• Year: As we know, the date comprises three elements, i.e., day, month, and year. Here, we enter the year in the form of an integer. For example, to get the date in the format “14-Feb-2023,” we must supply the integer 2023.
• Month: The month number. For the previous example, it is 02 (Feb). We can enter numbers 1 to 12 here.
• Day: The day of the date, i.e., 14. We can give values from 1 to 31.

How to use DATESERIAL Function in VBA?

Let’s look at how the DateSerial function is used in VBA.

Step 1: In this example, we write the following VBA code to store the date 15th Jan 2023 in cell A1.

Sub Date_Serial()
Range(“A1”).Value = DateSerial(2023, 1, 15)
End Sub

We have used the Range object to access cell A2 with the Value property. Here, store the date using the DateSerial function.

The arguments of the function are 2023(year), 01 (month), and 15 (day).

Step 2: Press the F5 key to execute the code and check the value we get in cell A1.

When we execute the code, we get the date 15-01-2023 in cell A1 based on the arguments provided to the DateSerial function.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples of VBA DateSerial Function

We will show you examples of using the DateSerial function in VBA.

Example #1

Assume we need to generate a date 22-02-2023 by using the DateSerial function. First, follow the steps listed below.

Step 1: Start themacro by naming the sub-procedure as follows.

Step 2: Declare a variable (DS) of data type Date using the DIM statement because the value returned by the DateSerial function is of type Date.

Step 3: We must assign a value once the variable has been declared. Hence, enter the variable “DS” followed by an equal sign (=).

Step 4: Enter the Date Serial function and provide the year, month, and day’s part.

Step 5: After assigning the value to the variable DS, we now need to show the result of this variable in the message box as follows.

The final code looks like the following.

Sub Date_Serial_Example1()
Dim DS As Date
DS = DateSerial(2023, 2, 22)
MsgBox DS
End Sub

Step 6: Run the above code by pressing the shortcut key, F5. Now, we will get the following result in the message box.

The result is in the format of dd-mm-yyyy. It is because our system date format in excel is set to dd-mm-yyyy. But the output may vary based on the user’s date setting in their device.

However, we can get the desired date format using the VBA FORMAT function irrespective of the system date format. We will come to these examples later in this article.

Assign Year, Month, and Day to Different Variables

In the previous example, we entered the year, month, and day numbers directly into the DateSerial function. However, we can declare separate variables for each argument and then pass them to the date serial function.

For example, look at the following code.

Sub Date_Serial_Example2()
Dim DS As Date
Dim DS_Year As Integer
Dim DS_Month As Integer
Dim DS_Day As Integer
End Sub

Here we have declared three additional variables DS_Year, DS_Month, and DS_Day. Next, assign these variables respective values.

Sub Date_Serial_Example2()
Dim DS As Date
Dim DS_Year As Integer
Dim DS_Month As Integer
Dim DS_Day As Integer

DS_Year = 2023
DS_Month = 2
DS_Day = 22

End Sub

Next, assign these variable values through the DateSerial function to the variable DS of type Date.

Sub Date_Serial_Example2()
Dim DS As Date
Dim DS_Year As Integer
Dim DS_Month As Integer
Dim DS_Day As Integer
DS_Year = 2023
DS_Month = 2
DS_Day = 22
DS = DateSerial(DS_Year, DS_Month, DS_Day)
MsgBox DS
End Sub

When we run the code using F5, it shows the same result as the previous one. However, this is a more scalable solution if we change the values multiple times in the code.

Example #2 – Format the Date Returned by the DateSerial Function

The DateSerial function returns the date; the output is based on the system’s date format. However, one can change the VBA Dateserial format using the FORMAT function in VBA.

For example, look at the following DateSerial example code.

Sub Date_Serial_Format()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 2022
MyMonth = 5
MyDay = 10
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox MyDate
End Sub

Step 1: Execute the code. This code displays the date according to our system’s date settings.

Step 2: The date shown in the message box is unclear because we are unsure whether the first value represents the day or month unless we know the system date settings. To avoid this confusion, we use the FORMAT function to format the date.

Sub Date_Serial_Format()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 2022
MyMonth = 5
MyDay = 10
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox Format(Mydate, “DD-MMM-YYYY”)
End Sub

Step 3: We have surrounded the FORMAT function while displaying the output in the message box (marked in yellow). Now, execute the code. The result is displayed according to the format in the FORMAT function.

We can clearly understand that the month is May.

Example #3 – Interesting Scenarios with Date Serial Function

So far, we have learned that the DateSerial function will return the date based on the inputs provided. However, here are some interesting scenarios with the DateSerial function in VBA.

Scenario #1 – When the Year is in Single Digit

The output will be different when we enter the year in single digits. For example, look at the following code.

Sub Date_Serial_Scenario1()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 1
MyMonth = 6
MyDay = 12
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox Format(MyDate, “DD-MMM-YYYY”)
End Sub

For the variable MyYear, we have assigned the number 1. So, let’s run the code and see what output we get.

The year shown in the message box is 2001, even though we have provided the year as 1. This is because if the Year value is 0 – 99, it is interpreted between 1930 and 2029. Hence, 01 represents the year 2001, 02 represents the year 2002, and so on.

Scenario #2 – When the Month is 0

Let’s look at what happens when the month is 0.

Sub Date_Serial_Scenario2()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 2023
MyMonth = 0
MyDay = 12
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox Format(MyDate, “DD-MMM-YYYY”)
End Sub

In the above code, we have made the month 0 and the year 2023. Run the code and see what date we get in the output.

The date we got is “12-Dec-2022,” even though the month is 0. The reason for this, though, is when the month is zero, it takes the previous year’s last month, i.e., Dec, and the provided year also goes back to the last year from 2023 to 2022.

Scenario #3 – When the Month number is Beyond 12

We all know that there are only 12 months in a calendar year. So, let’s see what happens when we give the month number as 13.

Sub Date_Serial_Scenario3()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 2023
MyMonth = 13
MyDay = 12
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox Format(MyDate, “DD-MMM-YYYY”)
End Sub

The month number provided is 13; let’s run the code and see what date we get in return.

The result looks bizarre. Even though the year we provided is 2023, the date shows 2024.

The reason for this is when the month goes beyond the limit of 12, it pushes the year to the next year, i.e., from 2023 to 2024, and the first month of the following year will be taken as the month.

Hence, we get the date result as 12-Jan-2024. Similarly, when we make the month 14, the date will be 12-Feb-2024, and so on.

Scenario #4 – When the day is 0.

Look at the following code.

Sub Date_Serial_Scenario4()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 2023
MyMonth = 5
MyDay = 0
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox Format(MyDate, “DD-MMM-YYYY”)
End Sub

When the day is 0, we will get the following result.

Even though we have provided the month number as May (5) because we have mentioned the day as 0, it goes back to the last day of the previous month i.e., 30^th Apr.

Scenario #5 – When Day Goes Past 31

The maximum number of days we have in a month is 31, and the least is 28. So now we will see what happens if we enter the day number beyond 31.

Look at the following code.

Sub Date_Serial_Scenario5()
Dim MyDate As Date
Dim MyYear As Integer
Dim MyMonth As Integer
Dim MyDay As Integer
MyYear = 2023
MyMonth = 5
MyDay = 32
MyDate = DateSerial(MyYear, MyMonth, MyDay)
MsgBox Format(MyDate, “DD-MMM-YYYY”)
End Sub

The day we have provided is 32 for the month 5 i.e., May. Run the code and see what we get in return.

The date we have got is 01-Jun-2023. Because we have a maximum of 31 days in May, 32 goes to the first day of the next month, i.e., 01-Jun-2023.

If the day is 33, it will give us the date 02-Jun-2023.

Important Things to Note

• You cannot access the VBA Dateserial function in the Function category of your worksheet.
• DateSerial returns the date, but its formatting depends on the system date formatting.
• If the provided day is beyond 31, the day value will be taken as next month’s date, depending on how many extra days we have added beyond 31.
• If the month exceeds 12, the date will take the next year. For example, if you provide the month number as 14, it will take the Feb month of the next year.

The DateSerial function can only take integer values; hence, it throws an overflow error if the given values are beyond the VBA integer limit.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA DateSerial, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA DateSerial. Here we explain how to use DateSerial function in excel VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA Option Explicit
• VBA Select Cell
• VBA Format Number