While there are so many functions and options available in Google Sheets; there is no function built into it, that is particularly for maintaining and calculating time schedules.
But there is nothing to worry about. I will show you how you can still manage timing time logs and calculate timing differences with a few simple processes and steps.
It is safe to say that there are almost countless jobs that you can do on Google Sheets. From doing tasks such as data management, data reporting, calculations, statistical analysis and so much more; you will find Google Sheets to be an excellent, powerful and user-friendly spreadsheet program.
Maintaining hourly time schedules can be one of those tasks that can be done on Google Spreadsheet.
Hello and welcome to TeqTog and in this article, you can see how you can calculate time in Google Sheets!
Adjusting the Format in Google Sheets for Time
It is important you understand that, Google Sheets does not consider data in a cell as time data at first. Instead, Google Sheets will consider any data to be numeric data and perform calculations accordingly. Therefore, it is required for you to adjust the format of your cells.
Let me show you how you can do this.
Here I have a spreadsheet opened. Suppose I want to maintain the duration of hours provided to a client, for which I can charge my bills. So, I will need a column for clock-in and a column for clock-out and a third column to find out how many hours were worked.
1. I will select the entire first column. Then, I will go to the drop-down menu that says “123” on the second top menu bar. From the options in the “123” menu, I will apply the “Time” format.
2. Perform this me action for the second as well.
3. For the third column where I want to calculate the difference between the clock-in and clock-out time, I will format it with the option that says “Duration”.
4. Now that all of my columns have been adjusted in the relevant format, I can easily calculate time differences from my data.
I have my data ready and all I have to do now is find the difference between the clock-in time and clock-out time.
5. In the cell C2, I can give the formula
This will give me an accurate information about the time elapsed between the time two clocking points.
These results will be basis of my chargeable hours because they show the duration of time that has been worked.
Showing Time in 12 or 24 Hours Format in Google Sheets (Custom Number Format)
If you add hours more then 24, you may notice results is showing wrong. In that case you need to check the time format in google sheet.
To do this you can follow the steps bellow:
- Mark the cell/column/row you want to format for.
- Go-to format option from menu bar of the sheet as showing in the .gif video bellow.
- Select the Number and hover on it.
- Upon hover you can see under Number click on the “Custom Number Format” menu
- A dialog box will be pop-up for custom number format, enter the format: [hh]:mm:ss or hh:mm:ss
- Click on Apply to start use your custom format.
How to Add Hours as Time in Google Sheets
If you want to add hours in you sheets for any reason, you can’t simply add numbers as a hour in google workspace sheets. Let’s see it with a example:
You have a dataset where you have visitor ‘chock-in’ time and you know the hours they stayed that day before chock-out. Now you want to calculate the chock-out time by adding time in duration of stay in the column B.
If you add 5, 8, 2, 5, 12 as a duration of stay in hours and want to calculate and try to get chock-out time with any formula (SUM or Adding sign+) you will see a wrong calculation and it will show you 131:00:00.
Why this error happed?
Google sheets don’t consider the number as a hour, it’s count it direct number as day. And as you know a day=24 hours so 5days*24=120hours+clock-in time 11:00:00 in cell A2. So grand total 131:00:00. I hope you got it.
To fix this simply we need to divide the days with 24 hours to convert it into hours in the formula in cell B2.
So the adding formula will be look like:
Remember to do this correctly you need to pay attention to the time format if you face any problem to get same type of results.
How to Add Minutes as Time in Google Sheets
Like adding hours in Google Sheet you can add minutes as time. You just need to convert the numbers (date format) into minutes in the formula of adding. Like: Days*24*60=Minutes
For example, suppose you want to add 150 minutes (cell B2) with the cell A2 (11:00:00) and want to get result in the cell C2 like before.
You need to use this formula for adding minutes:
Now you can see Google Sheets calculated the correct time, means you added minutes as time successfully.
How to Add Seconds as Time in Google Sheets
You can add seconds as a time in the same process. Just need to converts the days into seconds in the adding formula.
Suppose you want to add 5000 seconds (cell B2) with clock-in time in the cell A2 11:00:00 and wants to see the clock-out time in the cell C2.
You need to use this formula in the C2 to get the time calculation.
Here days converted into seconds with 24*60*60*days=seconds.
Incorporating Dates in Calculating Time in Google Sheets
In addition to the method I have shown for calculating time, you can also incorporate information about dates to your time calculation. Doing so will be especially important if you have to maintain time logs of more than twenty-four hours or a single session/ shift in your job consists of more than one day.
See, by simply giving the input for the dates just on the left of the time data with a gap; you can easily incorporate dates in your calculation and, Google Sheet will give the results accordingly as well.
Subtract Time in Google Sheets
Is there a way to incorporate breaks taken by workers in my time log?
Yes, you can incorporate breaks in working hours by creating a separate calculation for breaks taken and, then incorporating that into your main hours worked calculation.
This is how you can achieve this:
- Create a log for maintaining breaks during work hours
- Now, for calculating the total net hours worked; just deduct the duration of each break from the duration of hours worked.
Simply put formula, B2–A2 is the total duration of hours worked and, F2–E2 is the the break time.
Therefore, the net hours actually worked would be
How can I convert minutes into fraction in Google Sheets?
When calculations of time involve incremental elements such as minutes, it is more convenient to convert them into fractions so that doing the relevant calculations can be simpler to understand.
You can format the figures into numerical values by selecting the data and, clicking on the “Format” option from the top menu bar. Then, select the “Number” option from the drop-down menu.
Is there a method I can use to find the shortest and greatest amount of time worked quickly?
If you need to find the shortest and greatest amount of time worked quickly, then you can do the following:
- Create two new columns for shortest time worked and the greatest time worked.
- Adjust the format of these two columns into “Duration” from the “123” menu.
- Apply the formula =MIN(cell range of duration worked) to the first column (shortest time worked) and the formula =MAX(cell range of duration worked) to the second column (greatest time worked).
MIN and MAX are functions of Google Sheets which identify the smallest and highest value from a data.
How to Calculate Total Hours Worked (Sum time in Google Sheets)
What if I want to know the total hours worked overall? Well, now we know the total hours worked for each day or each shift. But what if I need to know the total hours of services provided overall?
Once your time difference in duration format is converted, you can add the time values together. You may do this with any google sheets add formula, like: SUM Function or with addition sign (+), it’s up-to you.
There is a very simple way to do this.
- First, select a cell in which you want your answer.
- Format that cell into “Duration” in the same manner I already demonstrated to you earlier.
- Apply the summation function on the cell range that has the total hours worked.
=SUM(G2:G9) or =G2+G3+G4+G5+G6+G7+G8
If you want to learn about the summation function in Google Sheets along with other mathematical operators, then you can check out TeqTog’s article on Math in Google Sheets: Sum, Subtract, Multiply, Divide and Square
Although Google Sheets does not own its function for time calculation, there are simple ways you can still do this and also, incorporate information about time logs which crosses the 24 hour format.
I hope my this tutorial was able to help you in your learning journey to google office workspace and now you can calculate all types of times duration in your spreadsheets.
Our More Google Sheets Tutorials on: