Whether you're looking to create a quick financial spreadsheet or want to work with a colleague on an Excel-like document, Google Sheets is a great, free, web-based alternative to Excel, an app used by millions across the country to load data in a document, view and share it, and use math to track specific content with a spreadsheet.
One of the most useful aspects of spreadsheets is their flexibility. A spreadsheet can serve as a database, a calculation engine, a platform on which to do statistical modeling, a text editor, a media library, a to-do list, etc. The possibilities are almost endless. A particularly common use for spreadsheets, including Google Sheets, is time tracking such as employee hourly schedules or billable hours.
If you use Google Sheets to track time in this way, you will often need to calculate the difference between two timestamps, i.e. the time that has elapsed between two time events. For example, if someone clocked in at 9:15 a.m. and then clocked in at 16:30 p.m., they were on the clock for 7 hours and 15 minutes. If you must use Sheets for something like this, you'll quickly notice that it's not designed to handle those sorts of tasks.
Still, while Google Sheets isn't specifically designed to handle functions like this, it's 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'll use a timesheet format, showing the time the person started working, the time they left, and a (calculated) duration. You can see the spreadsheet I used for this example below:
Calculating time in Google Sheets
To measure the difference between two cells containing time data, Sheets must understand that the data in the cells is time data. Otherwise, it will calculate the difference between 9:00 and 10:00 as 100 rather than 60 minutes or one hour.
To do this, the time columns must be formatted as time and the duration column must be formatted as duration. Follow the steps below to set up your spreadsheet:
- Open your Google sheet.
- Select the first time column (Time In) and click on the '123' format drop-down list in the menu, then select Time as format.
- Repeat the operation for the second time column (Time Out).
- Format the Hours Worked column as Duration in the same way.
5. Columns are now formatted correctly to calculate the elapsed time between two recorded timestamps.
In our example, Time In is in column A, starting at A2, and Time Out is in column C, starting at C2. Hours worked are in column E. With the formats set correctly, the calculation couldn't be easier. All you have to do is use the following formula: '=(C2-A2)'. This will give you the elapsed time between the two cells and display it in hours.
You can take this calculation further by adding dates as well. This is useful if you have shifts that last longer than 24 hours or include two days in a single shift. To do this, set the Entry and Expiration time columns in Date Time format.
That's all we can say about it. By following the steps listed above and using the formula provided, it is incredibly easy to calculate time in Google Sheets.
Frequently Asked Questions
There's a lot you can do with Google Sheets, here are some of the most asked questions.
How to add breaks when calculating time?
When calculating hours worked by employees, it's helpful to know how to add breaks to your timesheet. There are several ways to add a lunch break to the hours worked by employees, we will discuss the method of starting and ending a break.
- Create the Start of break Column and add all breaks in cells.
You can just leave the column format on automatic, Google Sheets will do the rest.
2. Next, create the End of break Column and leave the format on automatic.
3. Calculate the hours for the Hours Worked column. So, E2 = (B2-A2) + (D2-C2). That is (Break Start – Time Start) + (Time Out – Break End) = Hours worked for the day. 
Calculate this for each row, so your Hours worked The column looks like this.
Donc, E3 = (B3-A3) + (D3-C3), etc.
How to convert minutes to fractions?
When dealing with time increments, it can be useful to be able to convert them to fractions instead of minutes, i.e. 30 minutes = 1/2. Converting minutes to fractions is easy, there are several ways to do it.
- Create a new cell, K2 in this example, and format it as Name.
2. Set the formula to '=(E2)*24'.
If you followed, the total should be 5,50 and look like this.
You can easily apply it to a group or column of cells and convert them in seconds.
How to find the shortest working time?
If you need to quickly locate the least worked time, this should help. The MIN() function is a built-in function that lets you quickly locate the minimum value in a list of numbers.
- Create a new cell and set it to Duration, I2 in this example, and assign the function '=MIN(E2:E12)' to it.
Assuming you follow the example, the Minimum hours worked The column should be 5:15:00.
You can easily apply MIN() or MAX() function to a column or a group of cells, try it by yourself.
How do you calculate the total number of hours worked?
If you're not familiar with programming or Excel, some of Google Sheets' built-in functions may seem strange. Fortunately, it doesn't take long to calculate the total number of hours worked. In this example, we will calculate the total number of hours worked by all employees in a day.
- Create a new cell and assign it as Duration, in this example cell G13.
2. In the Formula (fx) Cash: enter '=SUM(E2:E12)'. This will give you the total hours worked from cells E2 through E12. This is the standard syntax for Excel and various programming languages.
The total should be 67:20:00 and look like this: 
Latest Thoughts
Google Sheets was not specifically designed to be used as a timesheet, but can be easily configured to do so. This simple setup means you can track hours worked quickly and easily. When the time intervals cross the 24 hour mark, things get a bit more complicated, but Sheets can still achieve this by switching from Time to Date format.
I hope you found this tutorial useful. If you have, be sure to check out other TechJunkie articles to find the tips and tricks you need to get the most out of your favorite tech tools and apps.
(Want to know a person's age? Check out our tutorial on how to calculate age from a date of birth in Sheets. You can also read our article on determining how many days have passed between two dates in Sheets, or maybe you want to learn how to show today's date in Sheets.)
Got any other time tracking tips for Google Sheets? Tell us about them below!
