How to Use VBA TimeValue Function (6 Relevant Examples)

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.

VBA TimeValue Function Overview

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.

Formula

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

How to Insert VBA Code

Secondly, go to Insert>Module.

How to Insert VBA Code

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.

VBA TimeValue to Convert a Given String to Time Value


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

VBA TimeValue to Convert a Given String to Time Value

After running the code, you’ll get the output is 11:30:00 PM which is converted from 23:30.

VBA TimeValue to Convert a Given String to Time Value

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

Extract Time from the Combination of Date & Time

If you run the above code, the output will be 2:30:00 AM separated from “2/13/2022 2:30”.

Extract Time from the Combination of Date & Time

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

Find out Time from the Current Date & Time

Next, run the code and the found current time is 1:11:52 PM.

Find out Time from the Current Date & Time

Read More: VBA Date Function (12 Uses of Macros with Examples)


Similar Readings:


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

Get Time Serial Number

After running the code, the output will be as follows in the serial number.

Get Time 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.

Return Time Value on Worksheet 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

Return Time Value on Worksheet for a Single Cell

If you’ll do that and run the code, you’ll get the following output.

Return Time Value on Worksheet for a Single Cell

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.

VBA TimeValue to Return Time Value on Worksheet for Cell Range

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

VBA TimeValue to Return Time Value on Worksheet for Cell Range

After running the code, the output will be as follows.

VBA TimeValue to Return Time Value on Worksheet for Cell Range

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.

VBA TimeValue Error

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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo