What is the Excel TIMEVALUE Function?
The Excel TIMEVALUE function converts a time represented as text into a valid Excel time. Returns the decimal number ranging from 0 (zero) representing the times 0:00:00 (12:00:00 AM) to 0.999988425925926 representing the times 23:59:59 (11:59:59 PM).
TIMEVALUE Syntax
TIMEVALUE(time_text)
time_text, required, a time represented as text in one of Excel time formats. For example, “6:45 PM” and “18:45” is a text that represents the same TIME value but different format.
Usage notes:
- Any DATE value in the time_text argument is ignored.
- TIME value is a part of a DATE value, represented as a decimal number. For more information, please read “How does Excel Store Dates and Times”.
How to Use TIMEVALUE Function in Excel
For example, there is data like the picture below, date and time data in column A. What is the result of the TIMEVALUE function that uses data in column A as the time_text argument.
TIMEVALUE Function #1
=TIMEVALUE(A2)
Cell A2 contains a time represented in a text with HH:MI format. The result is 0.28125.
TIMEVALUE Function #2
=TIMEVALUE(A3)
Cell A3 contains a time represented in the text but with a different format HH:MI AM/PM. The result is 0.78125.
TIMEVALUE Function #3
=TIMEVALUE(A4)
Cell A4 contains the same data as cell A2, why does the TIMEVALUE function return an error #VALUE!? Look closely, the data in cell A2 is by default left aligned, this is a sign that data in cell A2 is a text.
Instead, the data in cell A4 is by default right aligned; this is a sign that data in cell A4 is a TIME value instead of a text.
TIME value in time_text argument causes the TIMEVALUE function to return an error.
TIMEVALUE Function #4
=TIMEVALUE(A5)
Just like cell A4, cell A5 contains a TIME value, but with a different format. The result remains an error #VALUE!.
TIMEVALUE Function #5
=TIMEVALUE(A6)
Cell A6 contains a text represents a date and time. The TIMEVALUE function only converts the time represented as text and ignores the date. The result is the same as cell A2, 0.28125
TIMEVALUE Function #6
=TIMEVALUE(A7)
Cell A7 contains a text that represents the date and time together, like cell A6 but with a different format. The result is the same as cell A3, 0.78125.
TIMEVALUE Function #7
=TIMEVALUE(A8)
Cell A8 contains DATE and TIME values simultaneously. Same as cell A4 and cell A5. The time_text argument that is filled with DATE and TIME value will return an error #VALUE!.
The complete results of Excel TIMEVALUE function in column B can be seen in the picture below.
