Check Sheet Logo
Check Sheet


The TIMEVALUE formula in Google Sheets is used to convert a time string into a time value in the format of HH:MM:SS. This formula is often used to extract the time value from a date/time string and use it for further calculations or formatting.


Use the TIMEVALUE formula with the syntax shown below, it has 1 required parameter:

  1. time_string (required):
    The time string that you want to convert into a time value. This string must be in a valid time format, such as "12:00 PM" or "15:30".


Here are a few example use cases that explain how to use the TIMEVALUE formula in Google Sheets.

Extracting time from a date/time string

You have a list of date/time strings in a Google Sheet and you want to extract only the time value from each cell. You can use the TIMEVALUE formula to do this.

Calculating time differences

You have a start time and an end time in different cells and you want to calculate the time difference between them. You can use the TIMEVALUE formula to convert the time strings into time values and then subtract them to get the time difference.

Formatting time values for display

You have a time value in a cell and you want to format it for display in a specific way, such as with AM/PM indicators or without seconds. You can use the TIMEVALUE formula to convert the time value into a time string and then apply formatting using the TEXT formula.

Common Mistakes

TIMEVALUE not working? Here are some common mistakes people make when using the TIMEVALUE Google Sheets Formula:

Incorrect time format

The time_string parameter must be in a valid time format such as 'HH:mm:ss AM/PM', 'HH:mm:ss' or 'HH:mm'. If the time_string format is incorrect, the formula will return a #VALUE! error. Make sure the time_string is in a valid format.

Invalid time_string parameter

If the time_string parameter is not a valid time string or is a cell reference that does not contain a valid time string, the formula will return a #VALUE! error. Double-check the time_string parameter to ensure it is a valid time string or a cell reference that contains a valid time string.

Incorrect timezone

If the time_string parameter contains a timezone, it will be ignored by the TIMEVALUE formula. Ensure that the time_string parameter does not contain a timezone or convert the time to the desired timezone before using the formula.

The following functions are similar to TIMEVALUE or are often used with it in a formula:

  • TIME

    The TIME function takes in three arguments: hour, minute, and second, and returns a time value. This function is commonly used in combination with other functions to perform time-related calculations.


    The DATEVALUE formula in Google Sheets converts a date string into a date value that Google Sheets can recognize as a date. This formula is commonly used when data is imported or copied into Google Sheets from other sources that may not recognize date values. The formula can be used to convert a variety of date string formats into a date value that Google Sheets can work with.

  • TEXT

    The TEXT formula is used to format a number into text using a specified format. The format can include special characters such as currency symbols, decimal points, and thousands separators. This formula is useful when you need to display numbers as text in a specific format.

  • HOUR

    The HOUR function returns the hour component of a given time. This function is most commonly used to extract the hour value from a timestamp or a time value. The function takes one parameter, which is the time to extract the hour component from.


    The MINUTE function in Google Sheets returns the minute of the hour for a given date or time value. It takes a single argument, time, which is the date or time value to extract the minute from.

Learn More

You can learn more about the TIMEVALUE Google Sheets function on Google Support.