Time Value in VBA is an inbuilt function which is also categorized under the date and time function in VBA, this function as the name suggests gives us the numerical value of the date provided as an argument, it takes a single argument which is the date and returns the numerical value from the argument.
What Does Time Value Function Do in VBA?
Time Value function in VBA Excel returns the time value portion from the full date and time. Date and time are stored as serial numbers in excel. The serial number represents the DATE and decimal represents the time. Using TimeValue function we can get only the time serial number i.e. decimal number.
Syntax of VBA TimeValue Function
The syntax of the VBA TimeValue function as follows.
As we said TimeValue function returns the serial number part of the given date which is stored as a text value. Time is nothing but the actual time we are looking to get the serial number. Remember Time Value can only get the serial number from the time which is stored as text, not as TIME.
Examples of TimeValue Function with Excel VBA
Below are the examples of the VBA TimeValue Function.
VBA TimeValue Example #1
Now, look at the simple example of the VBA TimeValue function.
Sub TIMEVALUE_Function_Example1() 'Convert the given input string to a valid time and display on the screen 'Variable declaration Dim MyTime As Date 'Assign time to a variable MyTime = TimeValue("28-05-2019 16:50:45") 'Display output on the screen MsgBox "Current Time is: " & MyTime, vbInformation, "VBA TIMEVALUE Function" End Sub
First thing I have declared the variable “MyTime” as Date.
Dim MyTime As Date
Then I have assigned the value to the variable by applying TimeValue.
MyTime = TimeValue("28-05-2019 16:50:45")
Then in the message box, I have assigned the variable result.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
MsgBox "Supplied Time is: " & MyTime, vbInformation, "TIMEVALUE Function".
If I run the code using F5 key or manually then, we will get the result as follows.
VBA TimeValue Example #2
For the same code, I will declare the VBA variable as “Double”.
Sub TIMEVALUE_Function_Example1() 'Convert the given input string to a valid time and display on the screen 'Variable declaration Dim MyTime As Double 'Assign time to a variable MyTime = TimeValue("28-05-2019 16:50:45") 'Display output on the screen MsgBox "Current Time is: " & MyTime, vbInformation, "VBA TIMEVALUE Function" End Sub
Now if I run the VBA code manually or by pressing the F5 key, it will display the serial number part of the time 16:50:45.
For your better understanding, I will first enter the numbers given by the VBA message box to one of the cells.
Now I will apply the time format to check the exact result.
When you convert it into a time format, you can see the exact time.
VBA TimeValue Example #3
Now, look at the below data.
We have data and time together from A1 to A14 cells. For the second column, we need to extract only the time value. Since we have more than one cell to deal with we need to employ loops to perform the same set of tasks for all the cells.
We have data from 1st cell to 14th cell, so our loop should run for 14 times. We need to use FOR NEXT loop in VBA to mention the lower limit and upper limit. The Below code is the already written code to extract time value from the date & time combination.
Sub TimeValue_Example3() Dim k As Integer For k = 1 To 14 Cells(k, 2).Value = TimeValue(Cells(k, 1).Value) Next k End Sub
When we run the code we will get the values like the below.
If you wish to see the time then apply TIME format to it.
So, this is how TIME VALUE function works in VBA & Excel.
This has been a guide to VBA TimeValue. Here we learn how to use VBA TimeValue function to return the serial number from the stored text value along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –