Prantick Bala
DateSerial function is an Excel time and date function which can be used in VBA to get a date from a specified year, month and day value. From this article, you will get to know the details and usages of the VBA DateSerial function.
The above image gives a general overview of the function. From the next section you will know the details of the VBA DateSerial function.
📂 Download Practice Workbook
Introduction to The VBA DateSerial Function
❑ Objective
VBA DateSerial function returns a date for a specified year, month and day.
❑ Syntax
DateSerial(Year As Integer, Month As Integer, Day As Integer)
❑ Argument Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| Year As Integer | Required | An integer number between 100 to 9999 that represents the year value of a date |
| Month As Integer | Required | An integer number that represents the month value of a date |
| Day As Integer | Required | An integer number that represents the day value of a date |
❑ Output
The DateSerial function will return date for the given value of year, month and day.
❑ Version
This function is available from Excel 2000. So, any newer version of Excel can run this function.
5 Examples of Using VBA DateSerial Function in Excel
Now, let’s see some examples of using the VBA DateSerial function which will help you understand the function more clearly.
1. Show Date in a Message Box by VBA DateSerial Function
You can display the date for a given year, month and date values by using the VBA DateSerial function. First,
➤ Press ALT+F11 to open the VBA window in your Excel.
After that,
➤ Click on the Insert tab of the VBA window and select Module from the expanded menu.
As a result, a Module(Code) window will be opened.
➤ Insert the following code in this module,
Sub My_Date()
Dim My_Date As Date
My_Date = DateSerial(2010, 5, 28)
MsgBox My_Date
End SubThe function will create a macro named My_Date which will show the date returned by the DateSerial function in a message box. Here the year argument is 2010, the month argument is 5 and the day argument is 28. So the DateSerial function will return the date 5/28/2010
At last,
➤ Click on the Run Sub/ UserForm icon or press F5 to run the code.
As a result, you’ll see the date is shown in a message box. In this manner, you can get the date in a message box for any year, month and date value.
Read more: How to Use the VBA DatePart Function in Excel
2. Show Date in Specific Format in a Message Box
You can also specify the format of the displayed date. Suppose you want to display the date in the format DD-MMM-YYYY. To do that,
➤ Insert the following code in the Module(Code) window,
Sub My_Date()
Dim Mydate As Date
Mydate = DateSerial(2021, 12, 3)
MsgBox Format(Mydate, "DD-MMM-YYYY")
End SubThe code will display the date returned by the DateSerial function in DD-MMM-YYYY format.
➤ Click on the Run Sub/ UserForm icon or press F5 to run the code.
As a result, you will see the date is now shown in the specified format.
Read more: How to Use VBA DateValue Function in Excel
3. Get Date in Worksheet by VBA DateSerial Function
You can also get the Date in your Worksheet by the DateSerial function. First,
➤ Insert the following code in the Module(Code) window,
Sub My_date()
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim dDate As Date
iYear = 2021
iMonth = 7
iDay = 25
dDate = DateSerial(iYear, iMonth, iDay)
Sheets("Example_sheet").Range("B3") = "Date is: " & dDate
End SubThe code will give the date in cell B3 of a worksheet named Example_sheet. In the code, we have declared variables of the arguments of the DateSerial function as iYear, iMonth and iDay and supplied values for the variables.
➤ Click on the Run Sub/ UserForm icon or press F5 to run the code and close the VBA window.
After closing the VBA window, you will see that the date is shown in the cell B3.
Read more: How to Use the VBA DateAdd Function in Excel
Similar Readings
- Format Date with VBA in Excel (4 Methods)
- How to Insert Current Date in Excel (3 Ways)
- Date Variable in VBA Codes (7 Uses of Macros with Examples)
- How to Get the Current Date in VBA (3 Ways)
4. Month Numbers Which Are Not Between 1 to 12
One of the amazing features of the DateSerial function is if you give a number which is not between 1 to 12 as the month argument the function doesn’t show an error. If you give 0 as the month argument, the function will show the last month of the previous year. If you give -1 as the month argument, it will give the previous month of the last month of the previous year.
In the image above, I’ve given -1 as the month argument. So the function gives an output which shows 11 as the month of the year 2017 (the previous year of 2018). The code of this image,
Sub My_Date()
Dim My_Date As Date
My_Date = DateSerial(2018, -1, 28)
MsgBox My_Date
End SubIf we give any number greater than 12, the function gives a month of the next year. For example, if we give 16 as the month arguments, the function will give the month 4 (April) of the next year.
Sub My_Date()
Dim My_Date As Date
My_Date = DateSerial(2018, 16, 28)
MsgBox My_Date
End Sub
Read more: How to Use Excel VBA MONTH Function
5. Day Numbers Less Than 1 or Greater Than Number of Days of the Month Argument
The Dateserial function gives a date of the previous month if you use 0 or any negative number as the day argument. For example, if we give -10 as the day argument, the function will give the date 20 of the previous month for a 30 day month and 21 for a 31 day month. If the month argument is 5 that means the previous month is April which is a 30 day month, the function will give 20 as the day.
Sub My_Date()
Dim My_Date As Date
My_Date = DateSerial(2018, 5, -10)
MsgBox My_Date
End Sub
Now, If you give any number greater than 30 for a 30 day month or greater than 31 for a 31 day month, the function will show a date of the next month. For example, if we give 40 as the day argument and 4 as the month argument the function will show the 10th of May.
Sub My_Date()
Dim My_Date As Date
My_Date = DateSerial(2018, 4, 40)
MsgBox My_Date
End Sub
Read more: How to Use the Day Function in Excel VBA
💡 Things to Remember
📌 Values from 0 to 49, inclusive, are considered as the years 2000–2049. The years 1950–1999 are represented by the default numbers between 50 and 99. This may vary according to the calendar of your computer. Use a four-digit year for all other year arguments (for example, 1960).
📌 The returned date component is in the current Visual Basic calendar’s time period units. If the Calendar property is set to Gregorian for year, month, and day, the given value is presumed to be Gregorian. The given value is presumed to be Hijri if the Calendar parameter is set to Hijri.
Conclusion
I hope now you know what the VBA DateSerial function is and how you can use the function in Excel. If you have any kind of confusion regarding this function, please feel free to leave a comment.
