The TIMEVALUE function converts a time to a decimal value between 0 (inclusive) and 1 (exclusive). The function can convert time stored as a string or a number.
Google Sheets works with times in formulas as fractions of a number, not as text strings. This function converts times into their usable form. For example, 12 PM would be .5, and 6 PM would be .75.
Although the inputs to the TIMEVALUE function can be valid times or text strings, they must be in one of the correct formats shown below. The times may have a 12-hour AM/PM time or 24-hour time.
There are Custom date and time formats available in the menus.
Use this Custom date and time formats menu to modify the appearance of the time.
⚠️ If you give the function a date and time, the spreadsheet will ignore the date and convert the time.
Contents
Syntax
=TIMEVALUE(time_text)
time_text The text representation of a time.
Related Functions
DATEVALUE – Converts a date to a numeric value similar to TIMEVALUE.
HOUR – Returns the hour value from a time
MINUTE – Returns the minute value from a time
SECOND – Returns the second value from a time
Errors
#VALUE! – The inputs aren’t a valid time, such as “A minute ago” or “Then”.
#NUM – An input is a number but is not a valid time.
Video Tutorial
Below is an example of this function creating an employee timesheet.
Examples
Example 1 – Simple Use of the TIMEVALUE Function
First, let’s look at the basic usage of the TIMEVALUE function.
In cell A1, we give the function the value "18:00". Because the time is typed directly into the formula, Google Sheets requires the surrounding quotes. Times must be surrounded by quotes, referenced in another cell, or produced by special time functions like NOW.
Example 2 – Using the TIMEVALUE Function on Text Strings
The ability to work on text strings is a strength of the TIMEVALUE function. Strings are text values instead of numbers. Let’s look at a few examples.
The values in cells A2 through A4 are text strings. You can tell because their default formatting is left-aligned instead of right-aligned. Also, if you used the ISTEXT function, it would return a TRUE.
Even though they are text values, Google Sheets can sometimes convert the times to a decimal. The strings in cells A2 and A3 can successfully be converted into 0.75 because they are in one of the time formats shown near the beginning of this page. However, 6 0' clock is not a valid time format and, therefore, cannot be converted by the TIMEVALUE function. Text strings that Google Sheets cannot convert to a time result in a #VALUE! error.
Example 3 – Extracting Time from Part of a String
If you’re working with system-generated dates, you may have time values inside of longer strings. Let’s look at an example of how to handle these strings.
The value we are after is 18:00:00, but we can’t access it without extracting it first. We’ll use the MID function, have it start at the 5th character, and return the following 8 characters using this formula: =TIMEVALUE(MID(A1,5,8)). The time value comes out as a string, then we convert it to a decimal with the TIMEVALUE function.
Example 4 – Date With a Time Value
Now let’s give the function a time and a date.
As you can see, TIMEVALUE extracts the time and eliminates the date. This comes in handy if you only want to deal with the time and not the date.
Live Examples in Sheets
Go to this spreadsheet for examples of the TIMEVALUE function shown above that you can study and use anywhere you want.