Tracking My Time

rani's picture

I've been tracking my time for the past few months. I'm able to do this perhaps because I'm just a housewife with plenty of spare time(Yeah right, the truth is, being a mom is a 24hr job!). Let me share with you about it.

In the beginning...

In the past I have been thinking about how to track my time, mainly to find clock in hours I spent doing different projects. However, no tools have struck me to be particularly useful. Some tools are too confined within one PC (i.e. time tracker in Google Desktop Sidebar), and some tools are too disconnected that they can't be synchronized with the PC (i.e. some pocket pc tracker program, or even manual notebook with pen). Or, I had to pay hefty price to buy time tracker software. In short, I needed a time tracker that can be accessed anytime anywhere and can aggregate the data into a report.

Using Twitter and Google Spreadsheet

Come Twitter and Google to the rescue. During the past months I have been using twitter to track my time together with Google Spreadsheet. Why twitter? Because I am able to access it from many different platforms, namely PC/Laptop, pocket PC / cellphone web browsers (with twitter mobile version), instant messenger, and SMS. I found that the cheapest way to input data offline is via pocket PC web browser connecting through GPRS/HSDPA/wifi into twitter mobile webpage. The webpage is only 3Kb long which means one pageview equals to 1 cent SGD. Of course the cheapest way is to input data through stationary PC, which is free.

Then I use Google Spreadsheet to aggregate the data into report. The problem with twitter is that I had to manually input the time tracking data into a Google spreadsheet in order to summarize it into a more sensible report. This is because I can't find any way for Google spreadsheet to import data from twitter. Although it only take me 30 minutes per week to input the data manually, I dreaded to do that because it's so boring.

Using Google Spreadsheet with Form Input

Anyway I just realized (too late) that Google Docs has launched new features on the spreadsheet since early February 2008. Now the spreadsheet can directly receive data via an online form webpage. This is cool because then I do not need to manually input twitter data into the spreadsheet. Moreover, the online form webpage is very simple, enabling me to open the form webpage in my pda quickly despite only using GPRS connection. The webpage is only 1.8Kb long, which means one pageview equals to 0.5 cents SGD, and I didn't have to spend time doing additional data entry. Basically, this enables me to create time tracking report on the fly.

Creating Time Tracker: Step-by-Step

First I created a new spreadsheet, with the first sheet to keep time-tracking data. I rename this first sheet as "time detail". Make sure that you have plenty of rows, say, 1000 rows, if you use the spreadsheet to track 24 hours of your life in great details. If you only use it to track only a few tasks, you might not need 1000 rows.But ensure you have enough rows, because that's what is written in the Google help instruction.

The form editing view

The form webpage view in browser

Then I created the online form according to the instructions. I made three input points, namely "Task", "Tag", and "Achievements/Notes", which will be depicted as three different columns in the spreadsheet. The "Task" column is to record the task name. "Tag" column is to assign three letter category into the corresponding task, and you can put as many category as you want. I have many categories, ranging from breastfeeding time to travel time, blogwalking, and categories dedicated for each separate projects. "Achievements/Notes" is to put down any additional notes on the task.

The grey rows are the ones input through the form, while the white ones are input directly on the spreadsheet

By using the form entry, Google also automatically creates new column within the sheet for timestamp, which marks the exact time when the particular entry is added into the spreadsheet from the webpage form.This timestamp marks the beginning of a particular task.

Then, in the column next to the ones used for form data entry, I added one column to calculate the duration of a particular task, using the formula =MOD(A4-A3,1) for row no.3, repeating this formula according to the next row numbers.

On top of the duration column I added a "total hours" data using a simple =SUM(E3:E1000)*24 formula, which will be used in the summary report later on.

Second, go to the second sheet and rename it as "summary". Then assign the columns to aggregate data according to the "Tag" by listing all the "Tag" that you have made.I made two columns for this, "Tag details" and the "Tag" itself.

The data, summarized according to the "Tag" or categories

Aggregating data is done by simply adding all the durations in the first sheet, depending on the assigned "Tag". This is done in the column next to the "Tag", using the formula =SUMIF('time detail'!C3:C1000,"Tag",'time detail'!E3:E1000)*24 (This formula will give the numbers of hours in decimal form, meaning the numbers behind the decimal point is the fraction of an hour. For example, 1.5 means one hour and a half, which is 1 hour and 30 minutes).

To make the number more sensible, I added two more columns "Percent" and "Daily Average". "Percent" shows how many percent of my time used for a specific task, and to make that number more tangible to me, "Daily Average" shows how many hours and minutes I spend each day for a specific task.

In summary, Google Spreadsheet with form webpage input enabled me to track my time both on and off my PC, even on the road (using my cellphone web browser), while generating summary report on the fly. Using flexible "Tag", this spreadsheet can be adapted both for extensive time tracking use or to only track a few projects.

Do you want me to create a Google Spreadsheet template to be shared publicly? Or do you have any advice to make the process easier? Let me know by writing your comments below.

The template spreadsheet is here, you can copy it into your own google account.



How about those tools used by lawyer. they work based on time, no?
I used the very simple one, microsoft outlook journal. it is automatic if I work at .doc or other office but not other. I have to put it manually.

thanks for sharing.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

This question is for testing whether you are a human visitor and to prevent automated spam submissions.