VBA TIMESERIAL Function

VBA TIMESERIAL Function

Objective of the VBA TIMESERIAL Function:

The VBA TIMESERIAL Function is a built-in function in MS Excel. It returns the Time from a supplied Hour, Minute and Second. 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 TIMESERIAL Function can be used in either procedure or function in a VBA editor window in Excel. We can use the VBA TIMESERIAL 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 TIMESERIAL Function, where we can use this TIMESERIAL Function and its real-time examples.

Table of Contents:

Syntax of the VBA TIMESERIAL Function

The syntax of the VBA TIMESERIAL Function is

TimeSerial(hour, minute, second)

In the above syntax all 3 arguments are mandatory. We must specify Hour, Minute and Second within parenthesis.

Parameters or Arguments

Where
Hour: This argument consists of an integer value from 0 through 23. It represents the Hour. If we specify any negative value, then negative value subtracts from the current Hour.
Minute: This argument consists of an integer value from 0 through 59. It represents the Minute. It accepts other values as well, not only between 0 to 59. Sometimes depending on input, the Hour parameter will change.
Second: This argument consists of an integer value from 0 through 59. It represents the Second. It accepts other values as well, not only between 0 to 59. Sometimes depending on input, the Minute and Hour parameters will change.

Where we can apply or use the VBA TIMESERIAL Function?

We can use this VBA TIMESERIAL 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 Time using TIMESERIAL function and display on the screen

Here is a simple example of the VBA TIMESERIAL Function. In this example we are using 3 parameters hour, minute and second. Finally, we are creating Time from those given three parameters. Here is the simple example of TIMESERIAL function.

'Returns a time from the given input Hour, Minute and Second and Dislay on the screen

    'Variable declaration
    Dim iHour As Integer
    Dim iMinute As Integer
    Dim iSecond As Integer
    Dim dDate As Date
        
    'Assign Hour, Minute and Second to multiple variables
    iHour = 11
    iMinute = 6
    iSecond = 20
    
    'Usage of TIMESERIAL function in VBA
    dDate = TimeSerial(iHour, iMinute, iSecond)
    
    'Display output on the screen
    MsgBox "Time is: " & dDate, vbInformation, "VBA TIMESERIAL Function"
  
End Sub 

In the above example ‘iHour’, ‘iMinute’ and iSecond’ are declared as an integer data type.
Output:
Here is the screen shot of first example output.
VBA TIMESERIAL Function Example 1

Example 2: Create Time using TIMESERIAL function and display on the Worksheet

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

Sub VBA_TIMESERIAL_Function_Example2()
'Returns a time from the given input Hour, Minute and Second and Dislay on the Worksheet

    'Variable declaration
    Dim iHour As Integer
    Dim iMinute As Integer
    Dim iSecond As Integer
    Dim dDate As Date
        
    'Assign Hour, Minute and Second to multiple variables
    iHour = 22
    iMinute = 24
    iSecond = 26
    
    'Usage of TIMESERIAL function in VBA
    dDate = TimeSerial(iHour, iMinute, iSecond)
    
    'Display output on the worksheet
    Sheets("VBAF1.COM").Range("B20") = "Time is: " & dDate
  
End Sub 

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

Example 3: Create Time using TIMESERIAL function and display on the screen

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

Sub VBA_TIMESERIAL_Function_Example3()
'Returns a time from the given input Hour, Minute and Second and Dislay on the screen

    'Variable declaration
    Dim iHour As Integer
    Dim iMinute As Integer
    Dim iSecond As Integer
    Dim dDate As Date
        
    'Assign Hour, Minute and Second to multiple variables
    iHour = 14
    iMinute = 70
    iSecond = 65
    
    'Usage of TIMESERIAL function in VBA
    dDate = TimeSerial(iHour, iMinute, iSecond)
    
    'Display output on the screen
    MsgBox "Time is: " & dDate, vbInformation, "VBA TIMESERIAL Function"
  
End Sub 

In general, we have 60 Minutes per Hour. But, in the above specified example we have provided 70 as a Minute input parameter. So, in the above example 60 mins added to hour and remaining 10mins showed as sminute.
Output:
Here is the screen shot of third example output.
VBA TIMESERIAL Function Example 3

Instructions

Here are the instructions to use the VBA TIMESERIAL 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 time on the screen.
  7. Ex2-Output: It displays time on the Worksheet in Rage B20.
  8. Ex3-Output: It displays time on the screen.
  9. Note: Observe Difference between first input and third input parameters

Download File

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

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 *