How to Track Hours in Google Sheets
When it comes to productivity, Google Sheets is like a Swiss army knife. There is rarely anything that you can’t manage by using Google Sheets.
And you can track hours in Google Sheets as well — maybe your working hours as a freelancer, exercising hours, or even sleeping hours.
In this post, I will show you a few simple yet effective ways to manage and track your hours in Google Sheets. At the end of the post, you can copy the sample Google Sheets to your account for free.
Hours tracker in Google Sheets
First, plan for what and how you will be tracking your time, start making all the entry columns, and it will all come to shape eventually.
Below, I will explain everything with a few examples. Let’s take a look…
#1. Tracking hours for daily tasks
If you want to keep track of how many hours you spend on daily tasks, it’s straightforward to set up in Google Sheets.
You create columns for date, task, start time, end time, total duration, and maybe a remarks or notes column. See the below screenshot of how it will look like…
Just the above-shown 7 columns and you are done!
In this, there is only one formula used, which is for calculating the duration between the start and end times. The formula is:
=IF(E2 = "", "", TEXT($E2-$D2, "hh:mm"))This formula simply calculates the time duration between the start and end time as soon as those details are entered. Yes, you may need to use custom date and time formats, though.
Not complicated, right?
#2. Tracking your daily sleep hours
While you can track your sleep hours by using the #1 tracker itself, I have a better idea for this.
How about you pre-fill all the dates and then just use checkboxes for the entry, something like this:
There are 26 columns as you see in the Google Sheets above — one for the date, 24 columns for the hours, and one for the total sleep hours calculated. You just have to check the hours when you slept and the total hours will automatically be calculated.
For calculating the sleep hour, the formula I have used is:
=if(COUNTIF(B2:Y2, true) < 1, "", COUNTIF(B2:Y2, true) - 1)I am sure you will find a less complicated way to get the same output.
On another note, we have created a more robust sleep tracker using Google Sheets (see it in the screenshot below). Apart from calculating the daily sleeping hours, it shows a color code for better visualization.
#3. Track hours worked on a project
If you are working on a weekly or a monthly project where you check in a few times a day and then work for a few hours/minutes, then you can track your work hours easily in Google Sheets.
You just need a system to input all your check-in and check-out times and then calculate the total hours worked every day.
Here’s a quick template that I created in Google Sheets…
I have assumed that a person will be checking in and out 3 times a day, as you see in the above screenshot. And again, there’s only one formula used in this simple hours tracker.
=(D2-C2)+(F2-E2)+(H2-G2)However, you will have to play with the custom date and time format to get the results in the desired format.
Related: Embed Google Sheets Charts to Notion without using any 3rd Party Tool
Wrapping it up
I hope you learned something from this quick how-to guide on tracking hours in Google Sheets.
You can click here to copy the above Google Sheets document to your Gmail account.
And if you get stuck anywhere, feel free to let me know in the comments below.
