Excel - TIMEVALUE Function
TIMEVALUE Function
The Excel TIMEVALUE function transforms the time, written as a text string, into an Excel Serial Number for a specific time. This function retrieves a decimal number between 0 and 0.99988426, which denotes the times value ranging from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). It is categorized under the Date & Time functions in Excel. This versatile function’s result can also be formatted using the TEXT function.
Compatibility
This advanced Excel function is compatible with the following versions of MS Excel −
- Excel for Microsoft 365
- Excel for Microsoft 365 for Mac
- Excel for the web
- Excel 2024
- Excel 2024 for Mac
- Excel 2021
- Excel 2021 for Mac
- Excel 2019
- Excel 2016
- Excel 2013
- Excel 2010
- Excel 2007
Syntax
The syntax of the TIMEVALUE function is as follows −
=TIMEVALUE(time_text)
Arguments
You can use the following arguments with the TIMEVALUE function −
| Argument | Description | Required / Optional |
|---|---|---|
| Time_text | It specifies a text string that denotes the time. The text string must be enclosed in the quotation marks. Otherwise, error may occur. | Required |
Points to Remember
- If the Date information is specified in the time_text, it is ignored.
- Time values are a part of a date and are indicated by a decimal number.
- If the supplied time_text contains an invalid time, then the TIMEVALUE function will retrieve the #VALUE! Error.
Examples of TIMEVALUE Function in Excel
Practice the following examples to learn how to use the TIMEVALUE function in Excel.
Example 1: What does the TIMEVALUE Function do in Excel?
In this example, we will insert the TIMEVALUE function into cell range C2:C4 to extract the decimal number from the input time values.
Solution
Step 1 − Assume the sample dataset comprises two columns titled "Time" and "Time Value".
Step 2 − After that, double-click on the C2 cell and enter the formula =TIMEVALUE("14:30:59").
Then, press the Enter tab to compute the TIMEVALUE function in excel.
Step 3 − Afterward, move to the next cell C3, and enter the formula =TIMEVALUE("14:30:45").
After pressing the Enter tab, the TIMEVALUE function will retrieve the 0.6046875 number of the time 14:30:45.
Step 4 − Moreover, you can write the formula =TIMEVALUE("-14:30:45") in the C4 cell.
Therefore, the TIMEVALUE function returns the #VALUE! error.
Example 2: Using the TEXT Function with the TIMEVALUE Function
In this example, we will format the resulting value of the TIMEVALUE function using the TEXT function.
Solution
Step 1 − Navigate to the "Sheet2" worksheet and enter the formula = TEXT (TIMEVALUE ("9:10 AM"), "hh: mm: ss") in the B2 cell. This expression transforms the text time 9:10 AM into a decimal value and further formats it into "9:10:00" time value.
And then, press the Enter tab to get the resulting value.
Step 2 − Let's say you wish to extract the time value in decimals of the current date and time. You may achieve this by using this expression =TIMEVALUE((TEXT(NOW(),"hh:mm"))) in the B3 cell.
This formula first extracts the current date and time in the hh:mm format, and then TIMEVALUE returns the decimal value of the time from the formatted current date and time value.
After that, press the Enter tab to obtain the result.
Download Practice Sheet
You can download and use the sample data sheet to practice the TIMEVALUE function.