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, free alternative to Excel.

One of the most useful aspects of spreadsheet programs is how flexible they are. A spreadsheet can serve as a database, calculation engine, platform for statistical modeling, text editor, media library, to-do list, and more. The possibilities are nearly endless. One common use for spreadsheets, including Google Sheets, is to track hourly employee time schedules or billable hours.

If you are using Google Sheets to track time in this way, you will frequently find yourself needing to calculate the difference between two timestamps, the amount of time that passed between two different 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 wasn’t built to handle these kinds of tasks.

Still, while Google Sheets was not explicitly designed to handle timing log functions, it is easy to persuade it to do so with a bit of preparation. This article shows you how to calculate the difference between two timestamps using Google Sheets automatically.

This article uses a timesheet format, showing the time the person started work, the time they left, and a (calculated) duration.

## Calculating Time in Google Sheets

Measuring the difference between two cells containing time data requires that Google Sheets understands that the cells’ data is time data. Otherwise, it calculates the difference between 9:00 AM and 10:00 AM as 100 rather than 60 minutes or one hour.

To properly count time differences, the time columns require formatting as Time and the duration column 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 the above step for the second (Time Out) time column.
- Format the Hours Worked column as
**Duration**in the same way.

5. 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**, starting at C2. The hours worked time is in column E. With the formats set correctly, doing the calculation couldn’t be easier. You need to use the following formula: *‘=(C2-A2)*‘. This formula gives you the elapsed time between the two cells and displays it as hours.

You can take this calculation one step further by adding dates too. This process is helpful if you have work shifts that go more than 24 hours or 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. Following the steps listed above and using the provided formula is relatively easy to calculate time in Google Sheets.

## How to Add Time Gaps or Work Breaks When Calculating Time

When calculating the hours worked by employees, it’s helpful to know how to add breaks to your timesheet. There are several ways to add lunch breaks into work hours.

- Create the
**Break Start**Column and add all of the breaks into the cells.

You can leave the column format set to automatic because Google Sheets handles the rest.

2. Next, create the **Break End** Column and leave the format on automatic.

3. Calculate the hours for the Hours Worked Column. So, **E2 = (B2-A2) + (D2-C2)**, which translates to “**Break Start” **– **Time Start**” + “**Time Out** – **Break End**” = **Hours Worked** for the day.

Use the calculation for every row so that your **Hours Worked** Column looks like this.

So, **E3 = (B3-A3) + (D3-C3)**, etc.

## How to Convert Minutes to Fractions

When dealing with increments of time, it might be helpful to convert them into fractions instead of minutes, i.e., 30 minutes = 1/2. Converting minutes to fractions is easy; there are several ways to accomplish this.

- Create a new cell,
**K2**in this example, and format it to**Number**.

2. Set the formula to ‘*= (E2) * 24*‘.

If you followed along, the total should be 5.50 and looks something like this:

You can easily apply this to a group or column of cells and convert them in a matter of seconds.

## How to Find the Shortest Amount of Time Worked?

If you quickly need to locate the least amount of time worked, this formula should help. The MIN() function is a built-in function that allows you to find the minimum value in a list of numbers.

- Create a new cell and set it to
**Duration**, I2 in this example, and assign it the function ‘*=MIN(E2:E12)*‘.

Assuming you followed the example, the **Min Hours Worked** Column should be 5:15:00.

You can easily apply the **MIN()** or **MAX()** function to a column or group of cells. Give it a try for yourself.

## How to Calculate the Total Hours Worked?

If you’re not familiar with programming or Excel, then some of the built-in functions for Google Sheets may seem strange. Luckily, it doesn’t take much to calculate the total hours worked. In this example, we’ll calculate the total hours worked by all the employees in a day.

- Create a new cell and assign it as
**Duration**, in this example cell**G13**.

2. In the **Formula (fx)** **Bar**: type ‘=*SUM(E2:E12)*,’ which provides the total hours worked from cells **E2** through **E12**. This formula is standard syntax for Excel and various programming languages.

The total should be 67:20:00 and look like this:

## Final Thoughts

Google Sheets wasn’t explicitly designed to produce timesheets, but it can be easily be 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!

Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.

## 21 thoughts on “How To Calculate Time in Google Sheets”

Example (mm:ss.ms) format:

B2 = 0:32.06, C2 = 0:31.60, D2 = 0:31.78

=MIN(B2:D2) >> Returns 0

Am unable to get Google Sheets to parse the “duration” cells properly or I do not understand how it does parse them more likely.

=(HOUR(C2)+(Minute(C2)/60)-(HOUR(A2)+((Minute(A2)/60))))

Appreciate the answer 🙂

I had the same problem as you and I think I figured it out. If you change your total hours worked cell to a number format and then multiply the number in that cell by 24, it should give you the correct hours in a plain number format that you can then multiply by the hourly rate.

Hopefully this works for you!

Lacey

Time In Time out Hours worked

1/12/2019 16:00:42 1/12/2019 17:00:42 1:00

Please include formulas in your answers