How to Use TIMEVALUE in Google Sheets (An Easy Guide)

Today, we will look at how we can use the TIMEVALUE function of Google Sheets. It is a simple function that can be applied to many different time-related calculations in a spreadsheet.

Basics of the TIMEVALUE function in Google Sheets

TIMEVALUE is a function in Google Sheets that takes a time string and returns a numerical value.

The function’s syntax:


the timevalue function syntax in google sheets

The time_string field holds the time representation. This field only takes a string value:

timevalue function only takes a string parameter or a cell reference to time

The time_string value can also be represented by a cell reference.

The most important thing to note is that the TIMEVALUE function sees the 24-hour day as a fraction, returning values between 0 (inclusive) and 1 (exclusive).

Here, the 0 value represents 12:00:00 AM and the 0.9999884259 represents 11:59:59 PM.

Note: When applying TIMEVALUE in cells that contain timestamps that include dates, the function will ignore the date portion:

timevlaue function only takes time values

Use of the TIMEVALUE Function in Google Sheets

Basic Example

Here we have a simple table of dates in the 24-hour format and have applied the TIMEVALUE function for each of them:


different values of time at different times of the day

As you can see, as we move down the day, the time value increases. With 6:00:00 being the ¼ of a day, we get the corresponding 0.25 value. 0.5 for 12:00:00 and 0.75 for 18:00:00 or 6 PM and so on.

Now, we can also use a Time format for these values to return them to their corresponding times.

Step 1: Select the TIMEVALUE values you want to convert. We have chosen the last two values.

selecting the timevalue values to convert

Step 2: Select the time format you want to convert this value to. We have navigated to the Time format (Time AM/PM) from the Format drop-down icon (123) from the Toolbar.

selecting the time format from the toolbar

Step 3: Select the default time format to update the TIMEVALUE fraction to a proper time format. For us, it was the AM/PM time value.

updated time format

Setting a Custom Time Format

Much like formatting custom dates, time formats can also be customized in Google Sheets according to the user’s needs.

To set a custom time format, we need to first select a cell containing a time value (optional) and then navigate to the “Custom date and time” option from the Format tab.

Format > Number > Custom date and time

navigating to custom date and time from the format tab

Now, in the Custom date and time formats window, we have a panel that contains how the current format of date or time is presented in our worksheet.

the custom date and time formats window

You can use the drop-down icon to see all the time options available. From here, you can choose the values you want to include in your time format and further customize them later.

the time formats that are available

Click Apply to apply the custom format on the selected cells.

Using TIMEVALUE for Different Time Formats in Google Sheets

As we have just seen, time can be presented in multiple different formats. But no matter the format, the underlying value remains the same. And that is what the TIMEVALUE function really uses.

Let’s have a look at some of the different but common time formats that can be seen in spreadsheets, and how the TIMEVALUE function reacts to them. Spoilers: There is little to no difference.

First up, we have the 24-hour time format in “HH:MM:SS” (Hours:Minutes:Seconds). This format is commonly used for the Arrival and Depart times for travel timetables.


using timevalue on a 24-hour format time in google sheets

Next, we have the 12-hour day format where the entire day is split into two 12-hour sections. The time format looks like “HH:MM:SS AM/PM”. This format is most commonly used for daily entry and leave times for employees.

using timevalue on a 12-hour format time in google sheets

Another way of presenting time is the Hour-Minute format, “HH:MM”. The seconds time value is often not counted since any change in that value is minuscule. Here, we can see a slight change in the last two TIMEVALUE results as the seconds are not considered.

using timevalue on a hour-minute format time in google sheets, the seconds are not counted

Finally, we have the Date-Time format, usually written as “MM/DD/YYYY HH:MM:SS”. This is the most data-heavy format for a cell. This is the default format of the NOW function.

using timevalue on a date-time format time in google sheets

As you can see, TIMEVALUE ignores the date and only focuses on extracting the value of the time.

Example: Calculating Total Time Worked

One of the most common uses of time values is to calculate the time elapsed or time difference between two times.

For our example, we have a sample dataset of an employee’s Entry and Leave timestamps for a week:

employee total hours worked dataset

We are looking to retrieve only the time value from the Date-Time timestamp and present it in the 12-hour, HH:MM:SS AM/PM, format. Eventually, we want to calculate the total time elapsed from the newly found In and Out times. And also, optionally calculate the total hours worked in the week.

Here are the steps:

Step 1: Retrieve the IN time from the Entry Date-Time column with the TIMEVALUE function. Use the fill handle to apply the formula to the rest of the column.

entering time in with the timevalue function

Step 2: Retrieve the OUT time from the Leave Date-Time column with the TIMEVALUE function. Once again, use the fill handle down the column.

entering the out time with the timevalue function

Step 3: Use simple formatting to convert these fractional time values in the Time IN and Time OUT columns to the 12-hour format.

updating the time format for time in and time out to 12-hour

Step 4: Use simple arithmetic subtraction between Time OUT and Time IN. This calculates the total hours worked.

calculating hours work by using simple arithmetic subtraction in google sheets

The calculated result may not be in the desired format. We will change that in the next step.

Step 5: Update the time format of the Hours Worked column, and subsequently the Total Hours Worked cell. From the Format toolbar option, we select Duration.

selecting the duration format for our hours worked

This results in the correct hours worked for each day. We have also used the SUM function to calculate the total.

total hours worked calculated with the sum function

Final Words

That concludes our simple guide to using the TIMEVALUE function in Google Sheets. It is a versatile function that can extract the value of time from any format to a fraction of 24 hours.

Feel free to leave any queries or advice you might have for us in the comments section below. Or have a look at some of our other articles that are sure to interest you.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply