What is TimeValue Function in VBA?
In VBA (Visual Basic for Applications), the “TimeValue” function is used to convert a valid time expression represented as a string into a time value that Excel can recognize and manipulate. It’s often used when working with dates and times in Excel macros.
See the example below.

Using the VBA TimeValue Function, we can view the time in 24-hour format by adding AM/PM after adding the date.

The default type of VBA TimeValue format is “hh:mm:ss” as seen above.
Table of Contents
Key Takeaways
- When extracting times from cells, ensure the cell format is compatible with TimeValue.
- To work with AM/PM notation, you can convert it into either 12-hour (provided you mention AM/PM explicitly) or 24-hour format before using TimeValue.
- Keep in mind that VBA’s TimeValue does not account for time zone differences.
- Always validate input data to avoid unexpected results or errors. Consider creating user-friendly error messages for users when dealing with invalid inputs.
- Test your VBA code with various time formats and edge cases to ensure accuracy.
Syntax of VBA TimeValue Function
The syntax of VBA TimeValue Variable is as shown below:
TimeValue(time_string)
Where,
- ‘time_string’ is a required parameter that represents the time expression you want to convert into a time value. It should be a valid time string in the format “hh:mm:ss” (hours, minutes, and seconds), where “hh” is the hour (00 to 23), “mm” is the minute (00 to 59), and “ss” is the second (00 to 59).
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use Time Value Function in VBA?
See how to use the VBA TimeValue function by following the steps mentioned below.
Step 1: Open the Excel WorkBook you’ll be working on. In the title bar, choose “Developer.”

In the Developer section, choose the “Visual Basic” option to open the Excel VBA Editor.

Once the Editor opens, in the toolbar, click “Insert” and select “Module.”

Now that the prerequisites are done let us see how to convert a given date to depict time accurately in different formats.
Step 2: Start by declaring a sub-procedure’s name.

Step 3: Declare a variable of String VBA data type and assign it a string in the valid time format.

Step 4: Declare a Date variable to convert the string variable to a Date variable.

Step 5: Print the time using a Message Box.

Print it in different formats using the VBA TimeValue Format function to print it in “hh:mm” and “hh:mm:ss.”
Here,
“vb” is, in short, Visual Basic.
“CrLf” is the Carriage Return and Line Feed, characters used in text files and communication protocols to indicate the end of a line and move the cursor to the beginning of the next line.
“vbInformation” is used to print an icon in the message box.
We used vbCrLf to print values in the same box but different lines.
Code:
Sub ExampleTimeValue()
Dim timeString As String
timeString = “15:30:45”
Dim myTime As Date
myTime = TimeValue(timeString)
MsgBox “Time Value: ” & vbCrLf & _
Format(myTime, “hh:mm AM/PM”) & vbCrLf & _
Format(myTime, “hh:mm:ss AM/PM”), vbInformation
End Sub
Step 6: Run the program by clicking the green arrow button or the “F5” button on your keyboard.

The output is printed in a message box as shown below.

Examples of Time Value Function with Excel VBA
Let’s see how you can use VBA TimeValue for your benefit by referring to the examples below.
Example #1
Given a date acquired using the VBA Now function in an Excel worksheet, print the different VBA TimeValue formats available in the adjacent cells.

Step 1: Create a subroutine to print VBA TimeValue milliseconds format.

Step 2: Assign the value of cell “A1” to a Variant data type.

Step 3: Assign the time value of the given date in a Date variable. The Date data type is the ideal VBA TimeValue variable data type.

Step 4: Print the formatted value of the VBA TimeValue in cell “B1”

Step 5: Print VBA TimeValue milliseconds format in cell “C1.”

The format to print time with milliseconds in Excel or Excel VBA is “hh:mm:ss.000.”
Where the points after the seconds depict a decimal point, depicting the number of milliseconds that have passed.
Code:
Sub GetTimeFromSheets()
Dim ipString As Variant
ipString = Range(“A1”).Value
Dim t As Date
t = TimeValue(ipString)
Range(“B1”).Value = Format(t, “hh:mm:ss”)
Range(“C1”).Value = Format(t, “h:mm:ss.000”)
End Sub
Step 6: Run the code and go to the worksheet to view the output.

Note: To view the hours and milliseconds format properly, you will need to format cells to do so manually.

Right-click on the cell and select “Format Cells.” It will open a new box.

Here, you can customize and enter in your preferred format.
Example #2
Given time values (in the string) in a given range, we want to calculate the total time by adding the time duration of all the values in the table and displaying the sum. This can be done by adding the values of the VBA TimeValue function.
The table is given below.

Step 1: Start with declaring a sub-procedure to find the sum of the time of all values combined.

Step 2: Declare 2 Date variables and a range variable to iterate through the table.

Step 3: Initialize a sub-procedure that calls the above function to perform VBA Today plus 7 days.

Step 4: Assign the sum variable as 0 or “midnight”.

We will add the time values to this variable.
Step 5: Initialize a for loop through the range of the table.

Check if the given values in Excel aren’t in Date format. If so, we can convert them into the Date format using the CDate function in Excel VBA.
Step 6: Add the sum value to the newly converted dates and iterate continuously.

Step 7: Print the sum value in a message box, formatted to print the hours and minutes only.

Code:
Sub CalculateTotalTime()
Dim totalDuration As Date
Dim cell As Range
Dim dte As Date
totalDuration = TimeValue(“00:00:00”)
For Each cell In Range(“A1:A10”)
If Not IsDate(cell.Value) Then
dte = CDate(cell.Value)
totalDuration = totalDuration + TimeValue(Format(dte, “hh:mm:ss”))
End If
Next cell
MsgBox “Total Duration: ” & Format(totalDuration, “hh:mm”)
End Sub
Step 9: Run the code and view the output below.

Important Things To Note
- Ensure that the input time string adheres to the “hh:mm:ss” format, with hours ranging from 00 to 23 and minutes and seconds ranging from 00 to 59.
- It’s essential to validate the input data for a valid time to prevent potential runtime errors. This validation helps ensure that the time string you provide to the TimeValue function accurately represents a time, minimizing the risk of unexpected issues.
- TimeValue exclusively focuses on the time component within a date-time value. When you use the TimeValue function, it disregards any date information that might be included in the input string, concentrating solely on the time of day.
- After using the TimeValue function to convert a time string, you can store the resulting time value in either Date or Variant variables.
- To present time values in a specific custom format when displaying them to users or within your VBA applications, you can use the Format function in conjunction with TimeValue.
- Regional variations may affect the format and syntax of time strings, so being aware of these settings helps ensure consistent and accurate results when using TimeValue.
Frequently Asked Questions (FAQs)
Yes. You can use the VBA TimeValue to convert a time with AM/PM using the VBA TimeValue variable. The format can either be written in a 12-hour or 24-hour format.
Sub ShowTime()
Debug.Print TimeValue(“12:30 PM”)
End Sub
Substitute the date for any date. It returns the value in a 24-hour format.
If you provide unrecognizable values for the VBA TimeValue format, you will experience a VBA TimeValue Type Mismatch error. VBA TimeValue function variable accepts either a String or Variant data type and returns a Date or Variant data type.
For example, TimeValue(12:30:00) is an invalid format.
No, the VBA TimeValue function is not case-sensitive for the time string; it can handle both uppercase and lowercase letters.
Yes, regional settings can affect TimeValue in VBA, particularly when interpreting time strings with different date and time formats.
Recommended Articles
This has been a guide to VBA TimeValue in Excel. Here we explain the How To Use Time Value Function in VBA using syntax along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply