Md. Abdul Kader
Often, we may have a time string in a format where the time value is not separate. In such a situation, using the VBA TimeValue function will be a better choice for you to return the time value from such a given time string. In this article, I’ll disclose 6 relevant examples with the necessary explanation of the VBA Time value function as well as its basics.
Firstly, let’s have a look at the overview of the VBA TimeValue function.
The above figure shows the overview of the utilization of the VBA TimeValue function. Before going into the main section, you may grasp the basics of the function.
Download Practice Workbook
Introduction to the VBA TimeValue Function
TimeValue, a built-in VBA function in Excel, separates time value from the given time as string. If the time string combines both date and time, the TimeValue function will extract the time value only. The following picture shows the condition when you wish to use the function in VBA.
- Function Objective:
Returns time value from the given time as a string.
- Syntax:
TimeValue(time)
- Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| time | Required | Refers to time as a string in a valid format |
- Return Value:
Returns time value and displays in custom format (not in serial number).
6 Examples of Using the VBA TimeValue Function in Excel
Right away, we’ll explore the uses of the function along with real-life examples. The first 4 examples display the output utilizing the Msgbox tool whereas the last 2 examples show the result on worksheets.
1. VBA TimeValue to Convert a Given String to Time Value
As we want to use VBA, we must know how to insert a VBA code.
Truly speaking, it’s a simple process.
Firstly, open a module by clicking Developer > Visual Basic.
Secondly, go to Insert>Module.
Now, you’re ready to insert a VBA code into the newly created module.
In the beginning example, you’ll see the process of converting a given string to the time value. However, the input string may be in two formats.
1.1. If the Input String is in 12-hours Format
If the input string is in 12-hours format, you may copy the following code.
Sub Convert_Given_String_to_Time_Value()
Dim A As Date
A = TimeValue("6:30 PM")
MsgBox A
End Sub
Next, run the code. Also, you may use the keyboard shortcut F5 or Fn+F5 (varies from the keyboards).
The output is below and it is 6:30:00 PM.
1.2. If the Input String is in 24-hours Format
When the string is in 24-hours format, look closely at the following code.
Sub Convert_Given_String_to_Time_Value()
Dim A As Date
A = TimeValue("23:30")
MsgBox A
End Sub
After running the code, you’ll get the output is 11:30:00 PM which is converted from 23:30.
Read More: How to Use VBA TimeSerial in Excel (3 Examples)
2. VBA TimeValue to Extract Time from the Combination of Date & Time
Sometimes the time string consists of both the date and time. You can separate the time from the combination easily using the VBA TimeValue function.
Just copy the following code.
Sub Extract_Time_from_DateTime()
Dim A As Date
A = TimeValue("2/13/2022 2:30")
MsgBox A
End Sub
If you run the above code, the output will be 2:30:00 AM separated from “2/13/2022 2:30”.
Read More: How to Use IsDate Function in VBA (3 Examples)
3. VBA TimeValue to Find out Time from the Current Date & Time
Generally, we use the NOW function to get the current date & time. More importantly, if you need to return the current time value you may use the following code.
Sub Finding_Time_from_Current_DateTime()
Dim A As Date
A = TimeValue(Now())
MsgBox A
End Sub
Next, run the code and the found current time is 1:11:52 PM.
Read More: VBA Date Function (12 Uses of Macros with Examples)
Similar Readings:
- How to Use the VBA Environ Function (4 Examples)
- Use VBA Int Function in Excel ( 3 Examples)
- How to Use the VBA Weekday Function (2 Suitable Examples)
- Use VBA Mod Operator (9 Examples)
- How to Unhide Top Rows in Excel (7 Methods)
4. VBA TimeValue to Get Time Serial Number
We knew earlier that the VBA TimeValue doesn’t display the time as a serial number.
But you can get the time serial number; if you need it.
For this, you have to fix the whole string as Double instead of Date.
So the adjusted code will be-
Sub Get_Time_Serial_Number()
Dim A As Double
A = TimeValue("2/13/2022 2:30")
MsgBox A
End Sub
After running the code, the output will be as follows in the serial number.
Related Content: How to Use VBA IsNumeric Function (9 Examples)
5. VBA TimeValue to Return Time Value on Worksheet for a Single Cell
Assuming that you have to extract the Time Value (Delivery time only) from the given Delivery Date-Time for a single cell.
In such a case, you may use the following code.
Sub TimeValue_SingleCell()
Range("D5").Value = TimeValue(Range("C5"))
End Sub
If you’ll do that and run the code, you’ll get the following output.
Related Content: How to Return a Value in VBA Function (Both Array and Non-Array Values)
6. VBA TimeValue to Return Time Value on Worksheet for Cell Range
Furthermore, you may have the cell range of Delivery Date-Time, and you need to separate the Time Value.
You may utilize the following code to extract time from the given string.
Sub TimeValue_Worksheet()
Dim k As Integer
For k = 5 To 16
Cells(k, 4).Value = TimeValue(Cells(k, 3).Value)
Next k
End Sub
After running the code, the output will be as follows.
Related Content: How to Use VBA FileDateTime Function in Excel (3 Uses)
Things to Remember
1. If the value of time argument is not valid, Excel will show the error ie Run-time error ’13’: Type mismatch like the following screenshot.
2. Don’t be confused about the TIMEVALUE function in Excel and the VBA TimeValue function. The first one returns a time serial number and the second one also extracts the time serial number but shows that serial number in the custom format.
Conclusion
In the above article, I tried to cover the necessary examples of the VBA TimeValue function along with the ins and outs of the function. I strongly believe that this article will articulate your Excel journey. However, if you have any queries or suggestions, please let me know in the comments section below.
