In this tutorial, you will learn how to calculate time in Google Sheets.
How to Calculate Time in Google Sheets
Quick Navigation
Have you ever wanted to calculated the time elapsed between two specific times? For example, you may have a tracker that logs the time employees time in and time out of work. You may want to know which employees have been prone of underworking or overworking.
Google Sheets allows you to easily find the duration between two times by using the subtraction operation.
In this guide, we will explain how to compute for the time in Google Sheets. We will also explain how to convert the result into different units of time such as hours, minutes, and second.
How to Compute the Time in Google Sheets
Here’s how to compute the time in Google Sheets.
Step 1
We’ll start by understanding how to calculate the difference between two time values. First, the user must ensure that both time values are properly formatted.
We can find the Time number formatting in the More Formats shortcut in the toolbar.
Step 2
Next, select an empty cell where we will place the result of our calculation.
Step 3
The simplest way to find the difference between two time values is by using the subtraction operator.
To ensure that the result is a positive number, we’ll subtract the start time from the end time value.
Step 4
You may have noticed that the time duration is written as a time format as well. We can change the number formatting to Duration to display the right values.
After applying the proper format, we can now determine that the duration between the start time and end time is about an hour and 44 minutes.
Step 5
Alternatively, we can use the TEXT function to automatically convert the difference into our preferred format.
Step 6
We can convert the time duration to hours by multiplying the result by 24.
Step 7
We can convert the time duration to minutes by multiplying the result by 24 and multiplying once more by 60.
Step 8
We can convert the time duration to seconds by multiplying the result by 60, 60 again, then 24.
Step 9
We can retrieve each individual time unit from the time duration by using the HOUR, MINUTE, and SECOND
Summary
This guide should be everything you need to calculate time in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.