VBA DATESERIAL Function

VBA DATESERIAL Function

Objective of the VBA DATESERIAL Function:

The VBA DATESERIAL Function is a built-in function in MS Excel. It returns a date value from a supplied year, month and day. It has three input arguments or parameters. In these three arguments all parameters are mandatory. It is a ‘Data and Time’ type function. The VBA DATESERIAL Function can be used in either procedure or function in a VBA editor window in Excel. We can use the VBA DATESERIAL Function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the DATESERIAL Function, where we can use this DATESERIAL Function and its real-time examples.

Table of Contents:

Syntax of the VBA DATESERIAL Function

The syntax of the VBA DATESERIAL Function is

DateSerial(year, month, day)

In the above syntax all 3 arguments are mandatory. We have to specify year, month and day .

Parameters or Arguments

Where
Year: This argument consists of an integer value from 1 through 9999. It represents the year. If we specify any negative value, then negative value subtracts from the current year.
month: This argument consists of an integer value from 1 through 12. It represents the month. It accepts other values as well, not only between 1 to 12. Sometimes depending on input, the year parameter will change. Here are some use cases for the same.

  • If month value is 13, it represents January of the following year.
  • If month value is 1, it represents January of the calculated year.
  • If month value is 0, it represents December of the previous year.
  • If month value is -1, it represents November of the previous year.

day: This argument consists of an integer value from 1 through 31. It represents the day. It accepts other values as well, not only between 1 to 31. Sometimes depending on input, the month and year parameters will change. Here are some use cases for the same.

  • If day value is 32 and month is May, it represents January of the calculated year.
  • If day value is 1, it represents January of the calculated year.
  • If day value is 0, it represents December of the previous year.
  • If day value is -1, it represents November of the previous year

Where we can apply or use the VBA DATESERIAL Function?

We can use this VBA DATESERIAL function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Create Date using DATESERIAL function and display on the screen

Here is a simple example of the VBA DATESERIAL Function. In this example we are using 3 parameters year, month and date. Finally, we are creating date from those three parameters. Here is the sample example of DATESERIAL function.

Sub VBA_DATESERIAL_Function_Example1()
'Returns a date from the given input Year, Month and Day and Display on the screen

    'Variable declaration
    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim dDate As Date
        
    'Assign Year, Month and Day to multiple variables
    iYear = 2017
    iMonth = 6
    iDay = 20
    
    'Usage of DATESERIAL function in VBA
    dDate = DateSerial(iYear, iMonth, iDay)
    
    'Display output on the screen
    MsgBox "Date is: " & dDate, vbInformation, "VBA DATESERIAL Function"
  
End Sub

In the above example ‘iYear’, ‘iMonth’ and iDay’ are declared as an integer data type.
Output:
Here is the screen shot of first example output.
VBA DATESERIAL Function Example 1

 

Example 2: Create Date using DATESERIAL function and display on the Worksheet

Here is another example of the VBA DATESERIAL Function. This below example macro uses the DATESERIAL Function and returns date on the Worksheet named ‘VBAF1.Com’ in Range ‘B20’.

Sub VBA_DATESERIAL_Function_Example2()
'Returns a date from the given input Year, Month and Day and Dislay on the Worksheet

    'Variable declaration
    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim dDate As Date
        
    'Assign Year, Month and Day to multiple variables
    iYear = 2017
    iMonth = 6
    iDay = 20
    
    'Usage of DATESERIAL function in VBA
    dDate = DateSerial(iYear, iMonth, iDay)
    
    'Display output on the worksheet
    Sheets("VBAF1.COM").Range("B20") = "Date is: " & dDate
  
End Sub

Output:
Here is the screen shot of second example output.
VBA DATESERIAL Function Example 2

 

Example 3: Create Date using DATESERIAL function and display on the screen

Here is one more example with VBA DATESERIAL Function. Finally, it returns date on the screen.

Sub VBA_DATESERIAL_Function_Example3()
'Returns a date from the given input Year, Month and Day and Display on the screen

    'Variable declaration
    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim dDate As Date
        
    'Assign Year, Month and Day to multiple variables
    iYear = 2017
    iMonth = 13
    iDay = 31
    
    'Usage of DATESERIAL function in VBA
    dDate = DateSerial(iYear, iMonth, iDay)
    
    'Display output on the screen
    MsgBox "Date is: " & dDate, vbInformation, "VBA DATESERIAL Function"
  
End Sub

In general, we have 12 months in a year. But, in the above specified example we have provided 13 as a month input parameter. So it consider next year January month.
Output:
Here is the screen shot of third example output.
VBA DATESERIAL Function Example 3

Instructions

Here are the instructions to use the VBA DATESERIAL Function in Excel

  1. Open an Excel workbook.
  2. Press Alt+F11 to open VBA Editor window.
  3. Go to Insert menu and click on module from the available options.
  4. Insert above specified macro example.
  5. Select code and click on ‘Run’ command or use Keyboard shortcut ‘F5’.
  6. Ex1-Output: It displays date based on given input parameters on the screen.
  7. Ex2-Output: It displays date based on given input parameters on the Worksheet 1t Range B20.
  8. Ex3-Output: It displays date based on given input parameters on the screen.

Download File

Click on following link to download free example excel workbook to learn more about the VBA DATESERIAL Function.

VBA DATESERIAL Function Examples

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.
List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *