How to Use VBA TimeSerial in Excel (3 Examples)

The TIMESERIAL in Excel is a built-in Date/Time function that can be used in VBA. The TIMESERIAL function returns a time or Variant (Date) given an Hour, Minute, and Second value as arguments. In this article, we will learn how to use the TIMESERIAL function in VBA to generate a time in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Introduction to the VBA TimeSerial Function

VBA-TimeSerial-Function

▶ Function Objective

TimeSerial function in Excel returns a time given an Hour, Minute, and Second value as arguments.

▶ Syntax

TimeSerial(Hour, Minute, Second)

▶ Argument Explanation

Argument Required/Optional Explanation
hour Required Variant(Integer). A number between 0 and 23, inclusive to represent the hour value of the time. If we provide any negative value, then the negative value will be subtracted from the current hour.
minute Required A numeric value or expression that represents the minute value of the time.
second Required A numeric value or expression that represents the second value of the time.

 ▶ Return Parameter

Returns a time value.


3 Suitable Examples of VBA TimeSerial in Excel

We can use the TimeSerial function in VBA to generate a time value to use for a particular purpose. We can use it like below.

1. Apply the VBA TimeSerial Function to Generate Time Value in Excel

Step 1:

  • First, we will select Visual Basic from the Developer We can also press ALT+F11 to open it.

Select Visual Basic from the Developer Tab

  • Now, click on the Insert button and select Module.

Click on the Insert Button and Select Module

Step 2:

  • Write down the following code in the window that appears.
Sub Time_Serial_Message_Box() 
   MsgBox TimeSerial(9, 30, 45)
 End Sub
  • We have inserted 12 as hour value (First Argument), 30 as minute value (Second Argument), and 45 as second value (Third Argument).
      • hour value starts from 0. That means 12:00:00 AM. So, 9 as hour value means 9 hours after 12:00:00 AM. Which is 9:00:00 AM.
      • We have inserted 30 as the minute
      • Our second value is 45.

VBA TimeSerial

Step 3:

  • If a window named Macro appears, just click on Run from that window.

Window Named Macro

  • Now, you will see a message or pop box appears with a time value 9:30:45 AM on it.

Message or Pop Box Appears with a Time Value

Read More: How to Use VBA TimeValue Function (6 Relevant Examples)


2. Use the VBA TimeSerial Function to Generate Time Value with Negative Numbers

If we provide any negative value as any one of the three arguments, then the negative value will be subtracted from the value. See the example below to understand how the VBA TimeSerial function does it.

Steps:

  • Insert the below code in the Module of the VBA.
Sub Time_Serial_Negative_Expression()
     MsgBox TimeSerial(12 - 9, -30, 45) 
End Sub
  • We have inserted 12-9 as hour value (First Argument), –30 as minute value (Second Argument), and 45 as second value (Third Argument).
    • hour value starts from 0. That means 12:00:00 AM. So, 12 as hour value means 12 hours after 12:00:00 AM. Which is 12:00:00 PM. But, in our case, it is 12-9. So, TimeSerial Function will subtract 9 hours from 12:00:00 PM. And the hour value will be 9 hours before 12:00:00 PM. That is 3:00:00 AM.
    • We have inserted –30 as the minute So, the TimeSerial function will subtract 30 minutes from our current time value which is 3:00:00 AM. So, our time value will be 2:30:00 AM.
    • Our second value is 45.
  • So, TimeValue will return 2:30:45 AM as time value.
  • We will now click on the Run button to execute the code.

Click on the Run Button to Execute the Code

  • Now, you will see a message or pop box appears with a time value of 2:30:45 AM on it.

Message or Pop Box Appears with a Time Value

Read More: Excel Formula to Generate Random Number (5 examples)


Similar Readings:


3. Perform the VBA TimeSerial Function to Generate Time Value with Exceeded Argument Values

When we specify a time for the TimeSerial function, each of the three arguments in the TimeSerial function should be in the range specified for that unit. The specified range for the hour argument is 0-23 and 0-59 for the minute and second arguments. But if any of our arguments exceeds the normal or specified range for that argument, the TimeSerial function will increase it to the next larger unit as an appropriate argument value. Here is an example below.

Steps:

  • Insert the below code in the Module of the VBA We will insert a time value in cell B4.
Sub Time_Serial_Exceeded_Argument() 
      Range("B4") = TimeSerial(3, 90, 0) 
End Sub
  • We have inserted 3 as hour value (First Argument), 90 as minute value (Second Argument), and 0 as second value (Third Argument).
    • hour value starts from 0. That means 12:00:00 AM. So, 3 as hour value means 3 hours after 12:00:00 AM. Which is 3:00:00 AM.
    • We have inserted 90 as the minute But the specified range for the minute is 0-59. But our value exceeds the upper bound of the range by 30 minutes. TimeSeries will interpret this value as 1 hour and 30 minutes. The 1 hour will be added to the current hour value of 3 and the hour value will be 4. The minute value will be 30. So, our time value will become 4:30:00 AM.
    • Our second value is 0.
  • So, TimeValue will return 4:30:00 AM as time value.
  • We will now click on the Run button to execute the code.

Excel VBA TimeSerial

  • Now, you will see cell B4 is populated with the time value of 4:30:00 AM.

Message or Pop Box Appears with a Time Value

Related Content: How to Return a Value in VBA Function (Both Array and Non-Array Values)


Things to Remember

  • If any one of the three arguments is outside the range of 32,768 to 32,767, an error will occur.
  • If the time specified by the three arguments generates a date that falls outside the acceptable range of dates, an error will occur.

Conclusion

In this article, we have learned to use the VBA TimeSerial function in Excel to generate time values. I hope from now on you can use the VBA TimeSerial function easily to generate time values in Excel. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo