How to Calculate Hours Worked in Google Sheets [2 Methods]

Today, we will learn how to calculate hours worked in Google Sheets. Google Sheets is a calculation engine, whether you are performing any mathematical operation or finding the hours worked by your worker. Google Sheets is efficient for finding hours worked, just you need to enter values. Go through the following guide on how to calculate hours worked in Google Sheets to find more details.

When Do We Need to Calculate Hours Worked?

Let’s suppose, you own a small company where several workers are working for different shifts and are paid hourly. Then it may become much more difficult to calculate their working hours manually. Therefore, Google Sheets is the way to find hours worked efficiently and free of cost. Let’s see how to calculate hours worked in Google Sheets.

How to Calculate Hours Worked in Google Sheets

There are two different methods in the following tutorial.

1. Calculate hours worked by simple subtraction
2. Calculate hours worked by the MINUS formula

1. Calculate Hours Worked using Simple Subtraction

In this method, we will simply subtract the Time Out from Time In to find the total working hours of the employee or participant.

Step 1

You see in the following example, we have data on Time in and Time Out for some employees for which we must find hours worked by them in Google Sheets. Firstly, place your cursor where you want to calculate the hours worked.

Step 2

Once you have located the cell where you want to calculate the working hours then take an equal sign to run the formula and give the cell reference of Time Out first as I have given in the following example.

Step 3

After giving the cell reference of Time Out, put a subtraction sign from your keyboard then give the cell reference of Time In to calculate the worked hours in Google Sheets.

Step 4

Once you have completed the formula, press the Enter key to get the result and drag the result over the other cells to get the result of other cells, but what do we see? We have found some complex values in decimal points as can be seen in the following picture while we were calculating work hours.

Step 5

When we subtract time in Google Sheets, we usually find such a result due to cell format but there’s nothing to worry about. To convert these decimal values into decimal time just multiply all the resultant values by 24.

Step 6

Now you can see the result in the following picture, we have found worked hours made by the employees according to their Time in and Time Out.

2. Calculate Hours Worked Using the MINUS Function

If you have the time to start work and End work, then you can simply calculate the working hours by MINUS formula. Below are the steps to find hours worked by using the MINUS formula.

Step 1

Similarly, as above, first place your cursor where you want to calculate the worked hours in Google Sheets.

Step 2

After pressing the cursor, write “Minus” with an equal sign to run the subtract formula in Google Sheets as I have written in the following screenshot.

Step 3

After starting the minus formula, give the cell reference of Time Out first as we need to subtract Time Out from Time In to find worked hours.

Step 4

After giving the cell reference of Time Out, give the cell reference of Time In and close the bracket. Don’t forget to separate these arguments with comma symbols as you can see in the example below.

Step 5

Now just press the Enter key to get the result, You can also drag this formula over the other cells to find the results. When we subtract time in Google Sheets you may either find decimal values or you may get the result in time format. The result in time may be correct but can confuse you by AM and PM as can be seen below.

Step 6

As we are calculating the working hours you may convert these resultant values into duration format. To format these cells, first select all these cells by hovering the mouse on it.

Step 7

After selecting the cells, look and find the “Format” tab in the menu bar of Google Sheets as highlighted below. Click on it to open it to format your cells.

Step 8

When you click on the “Format” tab from the menu bar of Google Sheets, a drop-down menu will drag down where you will see a “Number” option as highlighted below.

Step 9

When you click on this “Number” option, another menu will expand where you will see different kinds of cell formats, Select the “Duration” format from the list as I have selected in the following picture.

Step 10

Now you have gotten the actual results for working hours as can be seen in the following picture. In this simple, you can calculate the worked hours in Google Sheets.

Frequently Asked Questions

How to calculate hours worked excluding break time in Google Sheets?

Most brokers pay the employees hourly due to which they also exclude break time from their working hours, so if you have such a task then below is how to calculate hours worked excluding break time in Google Sheets.

Step 1

As you can see in the following example, we have attendance time for several employees including their break time. Now we need to find their working hours also excluding break time. Place your cursor where you want to get your result in Google Sheets.

Step 2

Here we will apply the formula to calculate the working hours of employees excluding break time. In our syntax first, we will subtract the Time Out from Time In by giving their cell reference in the following pattern.

Step 3

For excluding break time also subtract the break time from the subtracting value of Time Out and Time In. Again, place a subtract sign in your syntax and give the cell reference of break time as I have written in the following picture.

Step 4

There is nothing to do anything more, just press the Enter key to get the results, As you can see in the following picture, we have gotten working hours excluding break time.

How to calculate O.T. hours in Google Sheets?

It is common in most organizations that people are working extra with the regular hours, but they are paid for them. But how do you know their extra hours or O.T hours? Below are the steps to find O.T. hours.

Step 1

To calculate O.T hours, you will be required for working hours and regular hours as you can see in the following example, we have both of them so let’s calculate O.T hours in Google Sheets.

Step 2

Here we will use the “IF” function to calculate the O.T hours in Google Sheets. According to the syntax of the IF function, first, we will have to give the logical expression that we have written (If working hours are greater than regular hours) in the following example.

Note: Don’t forget to absolute the cell reference to fix the cell reference of regular hours to prevent errors.

Step 3

Now according to the syntax, we need to specify if the value is true where we have specified “Subtract working hours from regular hours” by giving their cell reference.

Step 4

Similarly, if there is a value in false then give it to “0” as I have mentioned in the following syntax.

Step 5

Now, you will get such a result that, if the value of working hours is greater than standard regular hours then it will automatically subtract it to calculate the O.T hours otherwise it will remain zero as can be seen in the result in the following picture.

Conclusion

In the above article on how to calculate hours worked in Google Sheets, we have covered a couple of methods of calculating the working hours and discussed the formatting of cells. Hope now you will not have any trouble with calculating hours worked in Google Sheets.