Calculate Time in Google Sheets (Time Difference, Hours Worked, Add)

Are you working on a project that requires you to calculate time in Google Sheets, but you don’t know where to start? Well, today is your lucky day, as this article has all the information you’re looking for and more.

One thing we have always found fascinating about Google Sheets is its flexibility, and here is what we mean. Google Sheets can serve as a database, a tool for statistical modeling, a calculation engine, and more. 

In terms of everyday uses, Google Sheets can be used to track hourly employee time schedules or billable hours. If your job entails tracking time in this manner, then there is every likelihood that you’ll need to calculate the time difference between two timestamps often. 

Let’s assume for a minute that an employee signed in at 9:15 AM and clocked out at 4:30 PM; it means that the employee worked for approximately 7 hours and 15 minutes. While this is pretty straightforward, if you try to use Sheets for something like this, you’ll find out that it wasn’t designed to handle this type of task. 

Thankfully, there is a way around that, and we will be excited to show you how you can seamlessly calculate time in Google Sheets. Read on as we delve deeper into today’s Google Sheets tutorial.

How to Add Times and Calculate Worked Time in Google Sheets

Now that we have given you a background on how to calculate time in Google Sheets, we need to jump into the practical aspects of this Google Sheets tutorial right away. 

For starters, when measuring the time difference between two cells containing time data in Google Sheets, you need to ensure that Google Sheets understand that the data in those cells relates to time. If you fail to do this, then you can expect Google Sheets to calculate the difference in time between 9:00 AM and 10:00 AM as 100 instead of 60 minutes or an hour. 

To avoid that, you need to format the time columns as Time and the duration column as Duration. We know this may sound a little confusing, but we are sure you’ll get the hang of this when we jump into the practical aspect of this tutorial. 

Let us also quickly add that the calculation is intentionally backward (time out-time), and that’s because you need to account for AM and PM transitions. The reason for doing this is so that you don’t want to end up with a negative number. 

Take, for instance, 3:00 PM – 10:00 AM, will give you 7.00 hours. On the flip side, 10:00 AM – 2:00 PM will give -7.00 hours. 

Here is how to format a timesheet that shows when an employee started work, the time he/she left, and the duration they worked. 

For this tutorial, we will use the following sample data: 

Step 1: Format the spreadsheet

As we mentioned earlier, you need to ensure that your spreadsheet is properly formatted before calculating time in Google Sheets. Here is how to go about that. Head to the column for Time in and select all the accompanying cells. 

With that done, head to the Format menu, select the 123 Number option and choose Time. 

If you did that correctly, the column should be formatted to show the time. Here is what ours looks like after executing the steps above. 

After formatting the Time In column, you also need to do the same for the Time Out column. For the Time out column, select the accompanying cells under that column. 

With the relevant cells selected, navigate to the Format menu and select the 123 Number. From the options available, select Time. 

Here is what the spreadsheet should look like after formatting: 

Having formatted the column for Time out, it’s imperative that we do the same for the column for Hours worked. 

So as you did for the other columns, select all the cells where you want to calculate the time for hours worked. 

Now, head to the Format menu, and select the option for “123 Number.” From the options available, select Duration as the format. 

Step 2: Calculate Hours worked

Now that we have formatted the cells appropriately, we are ready to calculate the hours worked. To do that effectively, we will use the following formula: 

=C2-B2

Note: C2 here represents Time out, while B2 represents time in. 

• Start by selecting the cell where you want the result generated. For this guide, we will use cell D2. 
• With that done, head to the formula bar and type the following formula. 

=C2-B2. 

After typing in the formula, hit the Enter button on your keyboard. Google Sheets should instantly generate the result in the relevant cell. 

Here is what our spreadsheet looks like after calculating the hours worked for cell D2: 

From the image above, you’ll notice we only generated results for cell D2. This means we also need to generate the result for the other cells. But instead of doing all that manually, we can save ourselves the stress by using Google Sheets autofill option. 

For those who haven’t used Google Sheets’ autofill option before, this video demo shows you exactly how to do it. 

Following all the steps detailed above and using the formula we showed you, anyone, regardless of their experience, can easily calculate time in Google Sheets. However, what if you want to add breaks to the calculation? How do you go about that? Read on, as we will get to that shortly. 

How to add time gaps or work breaks when calculating time in Google Sheets

If you run a business that doesn’t offer paid lunches, you need to calculate breaks in the worked hours. Regardless of whether break times are paid or unpaid, it makes sense to include separate entries for break-in and break-out instead of just using the Time In and Time Out for breaks. 

Note: Just like we did for the time in and time out calculations, we need to calculate time in reverse, i.e. Time out – Time in. Only that, this time, we will have break time entries in between the formulas.

Since we want to add time gaps before calculating time in Google Sheets, we need to include columns for those in our sample data. Here, take a look at our updated sample data. 

Step 1: Format relevant cells

Since we have added columns for Break out and Break in, we need to ensure that those columns are properly formatted. Here is how it’s done. 

Start by selecting the various cells in the column for Break Out. 

With that done, head to the Format menu, and select the option for “123 Number.” You’ll be presented with a list of options from which you need to select Time. 

If you did everything right, that column should be formatted appropriately. Here is what ours looks like: 

After formatting the column for Breakout, we need to do the same for the Break-in column. So go ahead and select the relevant cells underneath that column. 

Like you did for the previous columns, head to the Format menu and select the option for “123 Number.” From the options you’re presented with, select Time. 

Step 2: Calculate hours for the Hours worked column

With all the relevant columns now appropriately formatted, we are ready to calculate the hours for the ‘Hours worked” column. To make things easy, we will use the following formula: “=(C2-B2)+(E2-F2),”

Here is how to apply the above formula: 

Start by selecting the cell where you want the result generated. For this example, we will select cell D2. 

With the cell selected, navigate to the formula bar and type in the following formula: 

=(C2-B2)+(E2-F2)

After typing in the formula, all that is left to do is hit the Enter button on your keyboard. The result should be generated almost instantly. 

Here is what our spreadsheet looks like after executing the above steps: 

If you look closely at the image above, you’ll notice that we only generated the result for cell D2. Now, we need to generate results for the other cells. But doing that manually will simply waste our time. To make our job easy, we can use Google Sheets’ autofill option. 

The video below shows you exactly how to do that: 

Now, you see how easy it is to calculate time in Google Sheets, especially when working with a data set with time gaps or breaks. 

How to Add Dates to Your Timesheets in Google Sheets

If you’re working on a project where you’re required to add dates to work time entries, it will interest you that the process is pretty much the same as it is for adding time. The only difference is the formatting option. So instead of just choosing Time for the formatting option, you need to choose Date time. 

Converting minutes to decimals in Google Sheets

If you’re working on a data set where you need to factor increments in time, it’s always a good idea to convert them into decimals instead of just minutes. So instead of having “1 hour and 30 minutes, you can simply just have it as 1.5 hours. 

And just so you know, converting minutes to decimals in Google Sheets is easy. Think we are bluffing? See for yourself. 

We will use the same sample data we have been using so far. 

Step 1: Format column D

Start by selecting the cells in the Worked time column. Once you have done that, navigate to the Format menu, select the option for 123 Number, and change the format from Duration to Number. 

After executing these steps, you’ll typically find some weird characters in the cells. Please ignore those. 

Step 2: Type in the formula

Now, click on the first Worked time cell, i.e., cell D2, head to the formula bar, and type in the following formula:

=(C2-B2)*24+(E2-F2)*24

Done typing in the formula? Great. Now, hit the Enter button on your keyboard. Google Sheets will generate the result instantly. 

Here, take a look at our spreadsheet after the result was generated: 

With the result for cell D2 converted from minutes to decimals, we need to do the same for the other cells. To save time, we will use Google Sheets’ autofill option. 

The video below shows exactly how to do that: 

Finding the lowest or highest amount of time worked

There are specific scenarios when you need to locate the least amount of time worked or the highest amount of time worked. Whatever the case, Google Sheets has a unique formula you can use to find the lowest or highest amount of time worked. 

Let’s start off with finding the least amount of time worked. 

To find the least amount of time worked, we can use the MIN() function, a unique built-in function in Google Sheets that makes it insanely easy to find the minimum value in a spreadsheet. 

Here is how to apply the MIN() function to find the least amount of time worked. We will use the same sample data we used for other examples. 

Step 1: Create a new cell

The first thing you want to do is create a new cell and name it “Min time worked.” After creating the cell header, select the respective cells under that column and format it to duration. We already showed you how to format to Duration in the previous examples we covered. 

Step 2: type in the formula

After formatting your cells, select the cell where you’d like to generate the result for the least amount of time worked. With that done, you can go ahead to type in the following formula in the formula tab. 

=MIN(D2:D11)

Finally, hit the Enter button on your keyboard. The result should be generated almost instantly in the respective cell. 

Take a look at the image below to get an idea of what your spreadsheet should look like: 

From the result generated, you can clearly see that 4.50 minutes is the least amount of time worked. 

Now, let’s show you how to check the highest amount of time worked. 

Step 1: Create a new cell

Head to a new cell and create a new header titled “Max timed worked.” After creating the header, head to the Format menu, click on the option for 123 Number and choose Duration as your preferred format. 

Step 2: Type in the formula

To find the highest amount of time worked, type in the following formula in the formula tab:

=MAX(D2:D11)

After typing in the formula, hit the Enter button on your keyboard. Google Sheets should automatically generate the result in the respective cell. Here is what ours looks like: 

From the above image, you can clearly see that the highest amount of time worked is 8.17. 

From what we have covered so far, we are sure you’ll agree that finding the lowest or highest amount of time worked in Google Sheets is a walk in the park. 

How to calculate the total hours worked in Google Sheets

Do you always need to figure out the total hours worked by all your employees, either weekly or monthly? Well, Google Sheets makes it super easy to calculate the total hours worked. 

We will use the same sample data set we have been using so far to calculate the total hours worked in Google Sheets. Additionally, we will use the below formula to make our job easy: 

=SUM(D2:D11)

Step 1: Create a new cell

Since we want to calculate the total hours worked, we need to create a new cell. We will label the cell, “Total hours worked.” 

Step 2: Select the cell where you want to generate the result. 

We need to choose a cell where we want to generate the result. Since we already have a column for that, simply select a cell underneath that. For this guide, we will use cell I2. 

Step 3: Apply the formula

The next thing we want to do is apply the formula, which is easy. Simply head to the formula tab and type in the following formula: 

=SUM(D2:D11)

Step 4: Hit the Enter button

After entering the formula in the formula tab, simply hit the Enter button on your keyboard. Google Sheets will automatically generate the result in the chosen cell. Here, take a look at our spreadsheet after applying the formula. 

A close look at the image above clearly shows that all employees completed a total of 57.42 working hours. 

Adding Hours to Date/Time in Google Sheets

Adding hours to date/time in Google Sheets can be a bit tricky. Thankfully, after reading this section, you should be able to do it easily. 

Assuming you have the data set as shown below, where you have the timestamp in column A, and you’re required to add the number of hours in column B, here is how you can go about it: 

Unlike when adding dates in Google Sheets, you cannot simply start off by adding the hours to the timestamp. 

First, you must ensure that the unit of columns A and B are the same. For instance, if you add 3 to 9:00:00, you won’t magically get 12 PM. The reason is that when you add 3 to time, Google Sheets interprets that differently and adds three days to the time to which it’s added. 

To this end, when you’re tasked with adding hours in Google Sheets, you must ensure the number represents the hours, not days. Essentially, it means 24 hours will give us a day, while 12 hours will be 0.5 days. And it goes on and on. 

To put things simply, it means that when you have the time in hours, you’ll need to divide it by 24 to arrive at the correct value, which you can then add to the timestamp that has both day and time. 

To add hours in column B to the time in column A as shown in our sample data, using the following formula will give you the correct Google Sheets elapsed time:

=A2+B2/24

Here is how to go about it. 

Step 1: Choose the cell where you want the result generated 

First, you need to select the cell where you want to add hours to the date/time. With our sample dataset, we will select cell C2. 

Step 2: Apply the formula

After choosing the cell where you want the result generated, it’s time to apply the formula. Simply head to the formula bar and type in the following formula. 

=A2+B2/24

Step 3: Hit the Enter button

With your formula correctly entered in the formula bar, simply tap the Enter button on your keyboard. The result should be generated almost instantly. Take a look at the result generated in our spreadsheet after applying the above formula: 

To replicate the result for other cells, you can simply use Google Sheets’ autofill option. This will save you the hassle of doing it manually. Here is what your worksheet should look like after generating the result for other cells. 

Final thoughts

Even though Google Sheets wasn’t originally designed to produce timesheets, it’s exciting to know that you can tweak it to calculate time in Google Sheets. 

In today’s guide, we extensively covered how to calculate time in Google Sheets. We started off by showing you how to format your cells in Google Sheets correctly. After this, we showed you a comprehensive step-by-step process of calculating time in Google Sheets, complete with screenshots and video illustrations. 

We hope today’s guide has shown you how easy it is to calculate time in Google Sheets. 

For more exciting Google Sheets tutorials, feel free to explore our blog, as we have tons of Google Sheets tutorials that will help resolve some of the common issues people encounter with Google Sheets. 

Other Google Sheets Resources

• How to Calculate Standard Deviation in Google Sheets
• How to Calculate Weighted Average in Google Sheets
• How to Calculate Percentage Change in Google Sheets
• How to Compare Two Columns in Google Sheets (Finding Differences and Matches)
• Google Sheets Conditional Formatting Custom Formula (with Examples)
• How To Strikethrough In Google Sheets (With Examples)
• How To Create Drop-Down List In Google Sheets (With Examples)