ASM Arman
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.
VBA TimeSerial Function Overview
â–¶ 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.
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. Applying 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.
- Now, 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.
-
Step 3:
- If a window named Macro appears, just click on Run from that window.
- Now, you will see a message or pop box appear with a time value of 9:30:45 AM on it.
Read More: How to Use VBA TimeValue Function (6 Relevant Examples)
2. Using 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 the hour value (First Argument), –30 as the 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, the TimeSerial Function will subtract 9 hours from 12:00:00 PM. 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.
- Now, you will see a message or pop box appear with a time value of 2:30:45 AM on it.
Read More: Excel Formula to Generate Random Number (5 examples)
Similar Readings:
- How to Use VBA Rnd in Excel (4 Methods)
- Use the VBA Environ Function (4 Examples)
- How to Use VBA Int Function in Excel ( 3 Examples)
- Use the VBA Weekday Function (2 Suitable Examples)
- How to Use VBA Mod Operator (9 Examples)
3. Generating 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.
- Now, you will see cell B4 is populated with the time value of 4:30:00 AM.
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.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
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!!!
