Whether you’re looking to throw together a quick financial spreadsheet or you want to work together with a co-worker on an Excel-like document, Google Sheets is a great, web-based, and free alternative to Excel, an app used by millions around the country to load data into a document, view and share it, and use math to track specific content with a spreadsheet.
One of the most useful aspects of spreadsheet programs is how flexible they are. A spreadsheet can serve as a database, as a calculation engine, as a platform on which to do statistical modeling, as a text editor, as a media library, as a to-do list, and on and on. The possibilities are nearly endless. One particularly common use for spreadsheets, including Google Sheets, is for tracking time such as hourly employee time schedules or billable hours.
If you are using Google Sheets to track time in this way, then you will frequently find yourself needing to calculate the difference between two timestamps, that is, the amount of time that passed between two time events. For example, if someone clocked in at 9:15 AM and then clocked out at 4:30 PM, they were on the clock for 7 hours, 15 minutes. If you need to use Sheets for something like this, you’ll quickly notice that it is not built to handle these kinds of tasks.
Still, while Google Sheets is not designed specifically to handle functions like this, it is easy to persuade it to do so with a little preparation. In this article, I will show you how to automatically calculate the difference between two timestamps in Google Sheets.
For this article I will use a timesheet format, showing the time the person started work, the time they left, and a (calculated) duration. You can see the spreadsheet I used for this example below:
How to Calculate Time in Google Sheets
Measuring the difference between two cells containing time data requires that Sheets understands that the data in the cells is time data. Otherwise, it will calculate the difference between 9:00 AM and 10:00 AM as 100 rather than 60 minutes or one hour.
To do this, the time columns need to be formatted as Time and the duration column needs to be formatted as Duration. Follow the steps below to set up your spreadsheet:
- Open your Google sheet.
- Select the first (Time In) time column and click the ‘123’ format drop-down in the menu, then select Time as the format.
- Repeat for the second (Time Out) time column.
- Format the duration column as Duration in the same way.
Now the columns are formatted correctly to calculate elapsed time between two recorded timestamps.
In our example, Time In is in column A, beginning at A2, and Time Out is in column C, beginning at C2. The hours worked time is in column E. With the formats set correctly, doing the calculation couldn’t be easier. All you need to do is use the following formula: ‘=(C2-A2)’. This will give you the elapsed time between the two cells and display it as hours.
You can take this calculation further by adding dates too. This is useful if you have work shifts that go more than 24 hours or that include two days within a single shift. To do this, set the time in and time out columns to be Date Time format.
That’s all there is to it. By following the steps listed above and using the provided formula, it is incredibly easy to calculate time in Google Sheets.
Google Sheets wasn’t specifically designed to be used as a timesheet but can be easily configured to do just that. This simple setup means you can track hours worked quickly and easily. When time-spans cross over the 24-hour mark, things become a little more complicated, but Sheets can still pull it off by changing from Time to Date format.
Hopefully, you found this tutorial helpful. If you did, make sure to check out more TechJunkie articles to find the tips and tricks you need to make the most out of your favorite technological tools and applications.
(Want to figure out a person’s age? Check our tutorial on how to calculate age from a birthdate in Sheets. You can also read our article on figuring out how many days have passed between two dates in Sheets, or maybe you want to learn how to display today’s date in Sheets.)
Got any other time tracking tips for Google Sheets? Tell us about them below!