How to Track Your Time Using Google Sheets
Data analyze your life …
The big tech companies are collecting data about us. Supposedly, they know more about us than we do.
Around the time I turned forty, I started seeing Facebook posts on my feed for diabetes. I got alarmed and bought a diabetes test kit. Thankfully, I don’t have the malady.
Then, I started noticing ads for insect killers. I assumed the tech companies were collecting information about my neighbors and an invasion was on the way. I bought some insect repellent sprays and waited for the pests to arrive. I did see some ants, but that was two years later. I guess the data analysis was two years ahead.
The point is that tech companies collect data on us, but making sense of that data is not easy.
I have been collecting data on myself for some time. I keep track of food intake, weight, sleep, exercise, and more. However, it has always been a challenge to keep track of my whole day on an hourly basis.
I tried a few apps. But none met my needs. If I like the categories in one app, I like the charts in another app. Some apps don’t let you edit things, some have weird ways of showing the information, and others make it difficult to export data. Exporting data is a pain in itself.
A few months ago, I decided to give spreadsheets another try. I have come up with a system that works for me. I think it might help others.
I am sharing a version of the Time Tracker here.
You cannot edit the shared “View Only” version. But you can copy it to your own Google Drive and then edit it.
Let me walk you through how to build the time tracker.
Building the Time Tracker
We can start with the tasks you perform every day. You can break it up into three categories:
Now open a new Google Sheets document and create two sheets/tabs:
- Time Tracking
- Reporting
In the Reporting sheet, create the following rows and columns:
Now, let’s go to the Time Tracking sheet.
On the top row, we are going to add the dates.
In the first column, we are going to add the time intervals.
Notice, the dates are incrementing from right to left. We want to add new dates nearer to the left, so we will always see the latest dates first when we open Google Sheet.
I am using increments of half an hour. If you want, you can go at fifteen-minute intervals.
Now, let’s create a menu from our tasks.
Select any cell on the Google Sheet and right-click. You should see the “Data Validation” option.
When you click on Data Validation, you should see the following window:
Click on the “Enter a range or formula” and go to the Reporting Sheet and select the tasks.
Note: Even though the entries go to 18, I have selected until 30. It will make it easier to expand your task list in the future.
After you save the Data Validation, you should see a downward arrow in the cell.
Now, if you click on the arrow, you should get a menu of the tasks.
The drop-down menu helps you easily enter tasks into your cells. You don’t have to write your tasks anymore. Also, we are creating fixed categories to make data analysis easier.
Suppose you enter tasks manually. For your writing, you might have “writing,” “wrote,” “written,” “worked on my book,” etc. That means when you are trying to figure out how you spent your time, you have to come up with algorithms to cover all of the variations. If you set categories, then you will always have a standard language for a task.
Let’s add some color to the tasks.
Right-click the same cell and choose “Conditional formatting.”
Click on the “Cell is not empty” and change to fill the cell with red background and white text when the text is “Wrote Fiction.”
Now, if you choose “Wrote Fiction,” you’ll see that the cell will look like this:
You can right-click and add conditional formatting for all your tasks.
For this example, I have set up the following:
I used red for the productive tasks, an ugly color for the nonproductive tasks, and the “Cell is not empty” rule will cover the rest. But you can add any rule and color scheme for your tasks.
Note: How you order the rules matter. If you put the “Cell is not empty” at the top, it will supersede the other rules.
Now, our time tracking cell is ready. If you change the text, it will show different colors.
And, if you delete the text, it will go back to the standard background.
Now copy this cell to all the rows and columns.
You are ready to keep track of your time on Google Sheets.
Here is a random example of how it might look as you start tracking:
Reporting
Now let’s set up some reporting.
We are going to go the Reporting Sheet and set up the following formula:
=COUNTIF(‘Time Tracking’!$C$2:$I$49,A4)*0.5
Here is the explanation:
- COUNTIF counts a cell if it has the matching text inside it. Empty cells will not be counted.
- Range of Cells to Check: ‘Time Tracking’!$C$2:$I$49 tells the formula to look for the days-of-the-week cells. In the Time Tracking sheet, you’ll see that C2 is the upper left corner and I49 is the lower right corner. The $$ is to make the cell evaluation static. Otherwise, when you copy the formula to the next cell, the range will change to C3:I50. We want the formula constantly to evaluate C2:I49.
- Location of The Text to Match: A4 is the location of “Wrote Fiction.” We want the location to change when we copy the formula to the next cell. So, we are not using $$ for it.
- Time Interval: The 0.5 multiplication is the time interval per cell. If you use 15-minute intervals, then you will have to use 0.25.
- In summary, COUNTIF(Range of Cells to Check, Location of The Text to Match) * Time Interval.
Once you have set up the formula, you can copy it to the rest of the cells in the All Time-Hours column. The range will stay static, the location of text will change.
It should look like this:
On the total row, we sum everything up, so we can see how many hours have passed:
We use the sum to calculate percentages:
Notice the use of $ to make sure that the total is always taken from B3.
You have to use this to display percentages:
I take the percentages and calculate per day and per week hours to estimate how I am doing.
I keep track of the productive and nonproductive time.
I calculate the awake time, so I can see how much time I had.
For the Current Week columns, you can use the same formulas with the ranges changed.
The left side is collecting data for everything. The current week allows me to see the data for the week in progress.
Updating the Google Sheet for a New Week
At the beginning of every week, I add the coming week in the Time Tracking sheet and update the formulas in the Reporting sheet.
So, in the Reporting sheet, the following formula:
=COUNTIF(‘Time Tracking’!$C$2:$I$49,A4)*0.5
Becomes:
=COUNTIF(‘Time Tracking’!$C$2:$P$49,A4)*0.5
P49 is the cell in the lower right corner.
You can copy and paste for the rest of the cells to update.
The Plan Today column is a quick way for me to set up blocks of time for the day. It’s a rough plan for the day. Reporting doesn’t look at this column.
Visualizing Your Data
When you have collected some data for a few days or a few weeks, you can visualize the information like a data scientist.
For example, here is a column chart of various tasks:
You can create charts of different kinds:
Final Thoughts
You can set this up in an hour or so. Now that I have figured out all the pieces, I can set everything up from scratch in 15–20 minutes.
Once you have the system ready, it becomes easy to track your time. You can collect the data at the end of the day. But I spend most of my time at my computer, so I have the spreadsheet open as I tackle various tasks. Also, you can update your Google Sheet from your phone. So, even if you are not at your computer, you can use it.
I like the method because you can visually see what is going on in your life. As I mentioned, I use ugly colors to point out time-wasters. I started with green for my productive tasks, but red seems to make it more vibrant. “Paint the town red” comes to mind. You can choose the color scheme that works for you.
Some discoveries about my data surprised me. I used to think that I wasted time in front of the television. From the data, I found out that I waste more time browsing the internet, almost three times more than watching TV. So, systematic data collection can give you insights into your own life.
As mentioned above, you can copy the Time Tracker to your Google Drive and try it out.
The good thing about Google Sheets is that you can change it yourself. You don’t have to wait for a developer to fix things or add features.
Hope this helps!