VBA TimeValue

Updated on June 12, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

TimeValue in VBA is an inbuilt function, also categorized under the Date and Time function in VBA. As the name suggests, this function gives us the numerical value of the date provided as an argument. It takes a single argument, the date, and returns the numerical value from the argument.

What Does Time Value Function Do in VBA?

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.

Syntax of VBA TimeValue Function

The syntax of the VBA TimeValue function is as follows.

VBA Timevalue Syntax

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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of TimeValue Function with Excel VBA

Below are examples of the VBA TimeValue function.

You can download this VBA TimeValue Function Template here – VBA TimeValue Function Template

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
VBA TimeValue Example 1

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 1-1

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
VBA TimeValue Example 2-1

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.

VBA TimeValue Example 2

For your better understanding, we will first enter the numbers given by the VBA message box to one of the cells.

Example 2-2

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

Example 2-3

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

VBA TimeValue Example 2-4

VBA TimeValue Example #3

Now, look at the below data.

Example 3

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
Example 3-1

When we run the code, we will get the values below.

VBA TimeValue Example 3-1

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.

Recommended Articles

This article has been a guide to VBA TimeValue. Here, we learn how to use the VBA TimeValue function to return the serial number from the stored text value, with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –