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 `5`

th 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.