TIMEVALUE in Google Sheets

What is TIMEVALUE function in Google Sheets?

The TIMEVALUE function in Google Sheets is used to convert the time string into a decimal. This value represents the time as a fraction of a 24-hour day. You can perform data manipulations and calculations on time values in this format. To use the TIME in Google Sheets, you provide a time string as an argument.

=TIMEVALUE(“4:30 PM”)

You can either supply it in a 12 or 24-hour format. You get a result between zero and one, where 0 stands for 12:00:00 AM and 0.9999884259 stands for 11:59:59 PM.

The TIMEVALUE function in Google Sheets converts whatever time is entered as a text string into a numeric value. This value represents the time obtained as a fraction of a day.
The output of the TIMEVALUE function is a decimal number that represents a fraction of a 24-hour day that the time corresponds to.
The syntax of TIMEVALUE in Google Sheets is =TIMEVALUE(time_string)
time_string: This argument is a string representing a valid time. It can be in the 12-hour (AM/PM) or 24-hour format.
The function TIMEVALUE is useful when you must extract only the time portion of a date-time value.
It is also very useful when you must convert the time into deci

Syntax

You can supply the TIMEVALUE function with parameters in a variety of time formats. It can also have delimiters such as colons or periods. The syntax of the function is as follows:

=TIMEVALUE(time_string)

time_string – This argument represents time in the 12-hour or 24-hour format. Do not forget to add double quotes to the TIME string. It can also be a cell reference containing a time string,

How to Use TIMEVALUE Function in Google Sheets?

The TIMEVALUE function is a powerful tool for performing calculations and manipulations on time. It converts text-based time values into decimal numbers, which can then be used in calculations such as adding or subtracting time, finding time intervals, and so on. Here, we look at how to use TIMEVALUE in Google Sheets. You can insert the function and use it in two ways.

Using TIMEVALUE manually
Through the Google Menu bar

Using TIMEVALUE Manually

Step 1: As we all know, TIMEVALUE helps streamline time-related operations. Insert some time-based values in a Google Sheet.

Step 2: Ensure that the data you have entered is in the text format. Choose the cell where we must display the converted time value. Here, we choose B1.

Enter =TIMEVALUE(, and then the cell reference that contains the time string. Here, it is A1. Otherwise, you can directly enclose the time string in double quotes as shown. For example, =TIMEVALUE(“1:30 PM”)

Step 3: Press Enter. You get the result, which is the decimal value for the time string. Drag the formula to B2 as well to get the result for both the time strings.

Using the Google Menu bar

We can also enter the same function using the Google Menu bar.
Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Date” ➝ “TIMEVALUE.”
Enter the required arguments. Close the bracket and press the “Enter” key.

Examples

In the section, let us look at some examples where we supply the time string in different formats and perform various calculations on time.

Example #1 – Calculating Hours Worked

We can use TIMEVALUE in Google Sheets to calculate the hours worked by an employee in this example. Suppose you have the start time of the employee’s login in Column B. You then have his logout time in Column C. We must calculate his hours worked over that entire week.

Step 1: Convert the start times to Google Sheets’ time format in Column C and the end times to the format in Column D.

Enter =TIMEVALUE(B2) in cell D2.

Step 2: Press Enter and drag the formula to D6.

Step 3: Now, convert the end times similarly in Column E. Enter.

=TIMEVALUE(C2) in E2.

Step 4: Now, type = E2-D2 to calculate the employee’s work duration and drag the formula to F6 to get the hours worked throughout the week.

To show the result as a time, select Column F, go to the Format menu, choose Number, and then select Duration.

Example 2 – Adding Time Intervals

In this example, we calculate the time duration of some students’ breaks and add the time intervals to find their total breaks per day.

Step 1: Let us enter the data in a spreadsheet.

Step 2: Let us calculate the time of the break. Use the following formula in cell C2.

=(TIMEVALUE(B2)- TIMEVALUE(A2))

Step 3: Press Enter. Now, copy the formula to D3.

Step 4: Now, let us find the total duration of the breaks. Enter the following formula in cell C4.

=SUM(C2:C3). Press Enter.

We have formatted Column C as Format->Number->Duration to find the sum of the time intervals.

Example 3 – Extracting Time from Date

You can extract the time portion alone from a date-time format in Google Sheets. Such instances are helpful when you wish to obtain the time part alone for further calculations.

In Column A, we have a few strings in the date-time format.

Step 1: Go to cell B1. Type the following function in it.

=TIMEVALUE(A1).

Step 2: Press Enter. Now, drag the formula to B2.

Step 3: The TIMEVALUE function converts the date-time string into a time value fraction of a 24-hour day. Now, to show the time in a readable form, Go to Format → Number → Custom Format and choose the time format, as shown below.

You get the time in a readable format.

If the date and time displayed are in a different format, you can use the TEXT function in Google Sheets to convert it to a string. For instance,

=TIMEVALUE(TEXT(A1, “hh:mm:ss”)).

For example, in cell B4, we enter the above formula for what looks like a vague decimal number in A4. Press Enter.

TEXT(A4, “hh:mm:ss”) converts the date-time value in A4 to a time string in the format hh:mm:ss.
TIMEVALUE(): Converts the time string into a time value.

Important Things to Note

If you are wary of errors, use TIMEVALUE with IFERROR to handle text times, which may be in multiple formats.
TO dynamically convert the time data from external sources, use TIMEVALUE with IMPORTDATA.
One thing to remember is that the decimal value we get as an output represents the time as a day’s fraction in a 24-hour format.
If you’re calculating the time between days, for example, 4:00 PM on Day 1 and 5:00 AM on Day 2, you would use the formula by adding +1 as follows:

=(TIMEVALUE(“5:00 AM”) + 1) – TIMEVALUE(“4:00 PM”)

Frequently Asked Questions (FAQs)

What are the reasons for errors occurring when using the TIMEVALUE function in Google Sheets?

Some of the reasons for errors to occur when using TIMEVALUE in Google Sheets are:
1. The time string is not a valid time, such as 25:00 PM. In this case, you get the
2. #NUM error if the input is a number but not a valid time.
3. You get the #VALUE! Error if the time provided is of an invalid format.
4. If no value is supplied as a time string.
5. The time format provided is not of any form recognized by Google Sheets.

Can you add or subtract times using TIMEVALUE in Google Sheets?

Yes, you can very well add or subtract times using TIMEVALUE. The result you get when you perform a regular addition or subtraction using TIMEVALUE is a fraction of a day. To see the result in hours, you must multiply the result by 24 (for hours) or 1440 (for minutes). Else, go to Format -> Number -> Time and format the result in a readable mode. For instance, let us add 2 hours to 8:30 AM.

Us the following formula, =TIMEVALUE(“8:30 AM”) + (2/24). You will get the result as 10:30:00 AM

What are the benefits of using the TIMEVALUE function in Google Sheets?

1. The TIMEVALUE function allows you to perform mathematical calculations on time, which may be in text format, with ease and accuracy.
2. Any data that is date-time based but in text format can be converted into a compatible date or Google Sheets time format, that Google Sheets can understand.
3. If we import time-based data for different external sources, using TIMEVALUE can standardize the time data to ensure a uniform format in our sheet.

Download Template

This article must be helpful to understand the TIMEVALUE In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

HLOOKUP-In-Google-Sheets Download

TIMEVALUE in Google Sheets