VBA TimeValue

The TimeValue function in VBA Excel returns the time value portion from the full date and time. It stores the date and time as serial numbers in Excel. Moreover, the serial number represents the DATE. The decimal represents the time. Therefore, using the TimeValue function, we can get only the time serial number, i.e., decimal number.

The syntax of the VBA TimeValue function is as follows.

The TimeValue function returns the serial number part of the given date and stores it as a text value. Time is nothing but the actual time we are looking to get the serial number. Remember, the TimeValue function can only get the serial number from the time, which is stored as text, not as TIME.

Below are examples of the VBA TimeValue function.

VBA TimeValue Example #1

Now, look at the simple example of the VBA TimeValue function.

Code:

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, we have declared the variable "MyTime" as a date.

Dim MyTime As Date

Then, we assign the value to the variable by applying TimeValue.

MyTime = TimeValue("28-05-2019 16:50:45")

Then in the message box, we have assigned the variable result.

MsgBox "Supplied Time is: " & MyTime, vbInformation, "TIMEVALUE Function".

If we run the code using the F5 key or manually, we will get the result as follows.

VBA TimeValue Example #2

We will declare the VBA variable as "Double" for the same code.

Code:

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 we 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, we will first enter the numbers given by the VBA message box to one of the cells.

Now, we will apply the time format to check the exact result.

You can see when you convert it into a time format.

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 tasks for all the cells.

We have data from the 1st cell to the 14th cell, so our loop should run 14 times. We need to use FOR NEXT loop in VBA to mention the lower and upper limits. The Below code is the already written code to extract the time value from the date and time combination.

Code:

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 below.

If you wish to see the time, then apply TIME format to it.

So, this is how the TIMEVALUE function works in VBA and Excel.