In this article, we will see how to calculate Time in Google Sheets. There are some situations when you require to calculate time, say to calculate your workers’ wages. Google Sheets has made time calculation easy. In Google Sheets, you have to format the cells correctly to calculate time. If you know how to format the cells, you can easily add and subtract time in Google Sheets.
How to calculate Time in Google Sheets
Here, we will talk about how to:
- Add time in Google Sheets
- Subtract time in Google Sheets
Let’s start.
1] How to add time in Google Sheets
Let’s take an example in which you have to calculate the weekly wages of your employees. For this, you should know the total working duration of your workers for all 6 days. The screenshot provided below shows the sample data.
The steps to add time in Google Sheets are as follows:
- Open a blank spreadsheet in Google Sheets.
- Format the cells correctly.
- Enter your data.
- Apply the summation formula to the cell in which you want to display the result.
Let’s see all these steps in detail.
First, open a blank spreadsheet in Google Sheets. As we have explained earlier in this article, it is necessary to format the cells correctly in order to get the correct result. Therefore, the next step is to format the cells. Select the range of cells in which you want to enter your data and go to “Format > Number > Duration.” In our case, we have formatted the cells from B2 to B8.
After formatting the cells, enter the time duration. After entering the time duration, the last step is to calculate the sum of the time duration of all 6 days. For this, you have to use the SUM formula. Type the following SUM formula and hit Enter.
=SUM(B2:B7)
In the above formula, B2:B7 represents the cell range from B2 to B7. You have to type the cell range in your data accordingly. After that, Google Sheets will display the result in the selected cell.
This is how you can add time in Google Sheets. Now, let’s see how you can subtract time in Google Sheets.
2] How to subtract time in Google Sheets
Let’s say you have data that contains the in and out times of your workers. In this case, to calculate the total weekly wages, first, you have to calculate their total working duration for each day in a week. Here, you need to subtract time in Google Sheets. After that, you can add the time durations of all 6 days and calculate the weekly wages of your workers.
To subtract time, you need to enter both date and time. Therefore, the cell formatting should be done accordingly. The steps are as follows:
- Open Google Sheets and create a new blank spreadsheet in it.
- Format the cells to date and time.
- Enter your data.
- Calculate the time difference.
Let’s see all these steps in detail.
First, create a new blank spreadsheet in Google Sheets. Here, we have taken sample data of 6 employees with In and Out times (see the screenshot below).
To get the correct result, it is necessary that you format all the cells correctly. In the example that we have taken here, the cells with the In and Out times should be formatted by Date Time and the cells that display working hours should be formatted by Duration.
Select the cells that display the In and Out times and go to “Format > Number > Date Time.” After that, select all the cells that display the result (working hours) and go to “Format > Number > Duration.”
Now, enter data in the In and Out time columns. After formatting the cells by Date time, you have to enter date and time along with Am and PM. Google Sheets will automatically convert the 12-hour time into the 24-hour format. If not, format the cells again.
Now, apply the following formula to the first cell of the Working Hours column. In our case, it is cell D2.
=End Time-Start Time
In our case, the End time is in column C and the Start time is in column B. Therefore, the formula to be entered in cell D2 is as follows:
=C2-B2
After entering the formula, press Enter. You have to enter the cell address correctly as per your data. Otherwise, you will get an error. To copy the formula to the remaining cells of the same column, place your mouse cursor at the bottom right side of the selected cell. After that, press and hold the left click of your mouse and drag it to the bottom.
After copying the formula to the remaining cells, you will get your result. This is how you can calculate the time difference in Google Sheets.
Read: How to insert WordArt in Google Sheets using Google Drawings.
How do I sum hours in Google Sheets?
You can sum hours in Google Sheets by using the SUM formula. But before using it, you should format your cells correctly, otherwise, you will not get the correct result. We have explained a step-by-step method to sum hours in Google Sheets in this article.
How do you calculate time duration?
You can calculate time duration by calculating the difference between the end time and the start time. In Google Sheets, you can do it easily by formatting your cells by Date Time. The output cells should be formatted by Duration. After that, apply the subtraction formula and you will get your result. We have explained this in detail in this article.
Hope this helps.
Read next: How to count checkboxes in Google Sheets.
