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.
- How to use
- Examples of using
TIMEVALUEformula not working?
- Similar formulas to
TIMEVALUE formula with the syntax shown below, it has 1 required parameter:
- 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".
ExamplesHere 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
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.
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:
TIMEfunction 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.
DATEVALUEformula 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.
TEXTformula 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.
HOURfunction 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.
MINUTEfunction 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.
You can learn more about the
TIMEVALUE Google Sheets function on Google Support.