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 makes time calculations 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 where you need to calculate your employees’ weekly wages. 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 explained earlier in this article, you must format the cells correctly to get the correct result. Next, format the cells. Select the range of cells in which you want to enter your data and go to “Format > Number > Duration.” In this case, we formatted cells B2:B8.
After formatting the cells, enter the time duration. After entering the time duration, the last step is to calculate the total time for 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 includes your workers’ in and out times. In this case, to calculate total weekly wages, you first need to calculate each worker’s total working time for each day of the week. Here, you need to subtract the start time from the end time in Google Sheets. Then add the time durations for all 6 days to calculate your workers’ weekly wages.
To subtract time, enter both the date and time. Therefore, format the cells 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. Enter the cell address correctly based on 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 corner of the selected cell. Then press and hold the left mouse button and drag it down.
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.
Hope this helps.
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. This article explains a step-by-step method for summing hours in Google Sheets.
How do you calculate time duration?
You can calculate time duration by subtracting the start time from the end time. In Google Sheets, you can do it easily by formatting your cells as Date Time. The output cells should be formatted as Duration. Then apply the subtraction formula to get your result. We have explained this in detail in this article.
Read next: How to count checkboxes in Google Sheets.
