Are you looking to save time when calculating durations in Google Sheets? In this article, I’ll show you the essential steps to perform these calculations quickly and efficiently. You’ll become a true Google Sheets pro in no time!
Calculating Duration
If you’re like most people and use Google Sheets to track your working hours, it’s important to be able to calculate the duration between two given times. Luckily, Google Sheets makes these calculations a breeze. Here are the steps to follow:
Step 1
Select the cell where you want to display the duration and type the following formula: =B2-A2. In this example, the start time is in cell A2, and the end time is in cell B2.
Step 2
The duration will be calculated, but it may display in a strange format depending on the cell’s formatting. It might appear as a time with AM or PM, or even as a decimal number.
Step 3
To format the calculation cell properly as a duration, select it, open the “Format” menu, hover over the “Number” submenu to open it, and choose “Duration”.
Step 4
Your calculation will now display as a duration, with the hours, minutes, and seconds correctly indicated.
Converting to Decimal Time
Many time tracking and payroll systems require time to be entered or imported as decimal time, rather than the standard format where noon is written as 12:00 PM. In this case, simply changing the number format is not enough, and we need to perform an additional calculation to obtain decimal time.
Here’s how to proceed:
Step 1
First, we need to understand how times are stored in Google Sheets. All time calculations in Google Sheets are based on days, so in reality, one hour is equivalent to 1/24, twelve hours to 1/2, and a full day to 1. If we simply change the number format to display it as a decimal, it will show as a fraction of a day, rather than as a number of hours and fractions of hours. For example, 13:26 will display as 0.56 as it is slightly over half a day.
Step 2
To convert fractions of days into decimal time, simply multiply by 24 using the formula =C2*24, where the duration is stored in cell C2.
Step 3
The decimal time will now be correctly displayed.
Summary
There you have it! You now know how to calculate time in Google Sheets. With these tips, you can save time and be more efficient in your calculations. Feel free to check out other Google Sheets tutorials on Crawlan.com to learn more.
