How to Use VBA DateSerial Function in Excel (5 Easy Applications)

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.

VBA DateSerial

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)

SYNTAX

❑ 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.

MODULE

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 Sub

The 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.

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.

OUTPUT

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 Sub

The 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.

VBA DateSerial

As a result, you will see the date is now shown in the specified format.

OUTPUT

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 Sub

The 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.

VBA DateSerial

After closing the VBA window, you will see that the date is shown in the cell B3.

OUTPUT

Read more: How to Use the VBA DateAdd Function in Excel


Similar Readings


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.

VBA DateSerial

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 Sub

If 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

VBA DateSerial

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

VBA DateSerial

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

VBA DateSerial

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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo