In this tutorial, you will learn how to calculate time in Google Sheets.
When working in Google Sheets, you may find you need to perform time-based calculations. For example, if you track your time on a spreadsheet, it’s important to be able to calculate the duration between two times. Fortunately, Google Sheets makes it easy to calculate time. Below are some of the most frequently used time calculations in Google Sheets
Many people track work hours on a spreadsheet, so calculating duration is a frequent task. The formulas are simple, but the cells must be formatted correctly in order to read the resulting duration.
Follow these steps to calculate duration:
Select the cell you want to calculate the duration in and type the following formula: “=B2-A2”. In this case, the start time is in cell A2 and the end time is in cell B2
The duration will calculate, but may display oddly depending on the cell’s formatting. For instance, it may display as a time with AM or PM, or it may display as a decimal number
To properly format the calculation cell as a duration, select the it then open the Format menu, hover over the Number submenu to open it and choose Duration
The calculation will now display as a duration with hours, minutes and seconds
Convert To Decimal Time
Many time tracking and payroll systems require time to be entered or imported in decimal time, not the standard format where noon is written as 12:00 PM. In this case, simply changing the number formatting will not work and we’ll have to do an extra calculation to get the decimal time.
First we need to understand how times are stored in Google Sheets. All time calculations are done based on days in Google Sheets, so behind the scenes an hour is 1/24, 12 hours is 1/2 and a full day is 1. If we simply change the number formatting to decimal formatting for a duration, it will display as a fraction of a day, not a number of hours and fraction of hours. For example, 13:26 will display as 0.56 because it is slightly greater than half a day
To convert fractions of days to decimal time, simply multiply by 24 using the formula “=C2*24” where the duration is stored in cell C2
The decimal duration will now display correctly
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to calculate time in Google Sheets. Want more? Check out all the Google Sheets Tutorials.