TIMEVALUE Function – Google Sheets

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.

Function help text for TIMEVALUE

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.

Different formats available for time
Different Time Formats

There are Custom date and time formats available in the menus.

Custom Time Formats

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.

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.

The TIMEVALUE function converting a time to a decimal
Simple Usage of TIMEVALUE

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.

The TIMEVALUE function converting a time in another cell to a decimal
Time in Another Cell

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.

Three time strings. One isn't valid.
Three Text Representations of the Time

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.

A time value being extracted from a long string
Time Inside a String

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.

The TIMEVALUE function ignoring a date value
TIMEVALUE Ignoring 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.

Leave a Comment