Spreadsheet-based tool for tracking time

jwhendy

Spreadsheet-based tool for tracking time

post by jwhendy · 2011-01-10T23:55:28.976Z · LW · GW · Legacy · 1 comments

I'm not sure what interest others will have in this, but I developed a spreadsheet-based time-tracking tool a bit back for a friend. He wanted to see where he was spending his time -- work, sleep, time with his wife, recreation, kids, etc. I used it myself for a while, too, and found it informative. I think the primary benefits of doing something like this are:

Examining what you think you value compared to what you show, in practice, that you actually value in order to identify areas of improvement concerning time usage.
Improve efficiency. Target an area, implement an reduction method, and see how your efforts track with time spent on that area.
Self-policing. I found that just by tracking my time, I was able to reduce time spent in the "wasted time" category (stupid browsing, non-beneficial wiki-hopping, etc.), especially while at work being paid to do something else.

The spreadsheet is available HERE, and consists of three tabs, one for time input and two for statistics.

DailyInput: this tab is where data is input each day. I have default formulas set to make time entry as fast as possible. After entering a date, start and top time each day, you should only have to enter stop times for the rest of the day (trying and seeing for yourself is easier than explaining it. To override (e.g. if you have a gap or are on the next day), just manually enter a date/time in the cell, overwriting the formula.

The number of hours gets calculated and will work for multiple-day-spanning activities (like sleep).

You assign a category and can input a memo for each chunk of time. The categories are a drop down list created from a set of cells you define (in column H). I have an example list in the download above. Changing the cells containing categories automatically changes the drop-down list (I have it set to read up to 20 cells). You can manually fiddle with this via the Data -> Validation dialog box. Sleep and work should remain the top two entries if you like the non-sleep/work calculations discussed below.

Jan-Jun/Jul-Dec: the other two tabs (I just broke it up into 6 months to cut down on the number of rows per sheet) do some basic calculations on the daily input. You'll see that the total hours spent on a category appear as the data gets input, and a conditional setting highlights the current week's row in blue. At the bottom you will find the following for each category:

Hrs/day for the current week
Amount of time spent on the category in the current week in the form of a %
Another current week % calculation that discounts sleep/work (focuses on free time)
Running average hrs/day (based on the elapsed time since the first entry on the DailyInput tab)
Running average hrs/wk
Running non-sleep/work %

Anyway, just thought I'd share. Check it out, suggest improvements (or make the improvements yourself), post thoughts after trying to use it, etc. Please also share any known tools that are better than this. I tend to stick with open-source/free tools, and only know of Rachota from my searching and examination of time tracking software. A spreadsheet solution does make it absolute cake to generate any graphs/charts of progress as desired. And you can add on tabs to track through multiple years quite easily.

Lastly, please do mention any experiments you've done like this in the past... What did you learn? Did predictions match actual time spent? Did tracking in and of itself curb time spent "wasting time"? Any other interesting observations/experiences? Is this exercise a valuable endeavor, perhaps even for a short amount of time and what additional benefits do you see coming out of it? I'd be interested in these answers.

1 comments

Comments sorted by top scores.

comment by Petrus · 2011-01-21T04:59:22.467Z · LW(p) · GW(p)

Thank you for sharing this cleverly designed worksheet.

Have been playing with it for a couple of days now and am integrating it into my existing tracking sheet (where bodyweight and personal finances are among the other variables being tracked).

I have made a small modification to enable faster date- and time entry with keyboard shortcuts. Save the spreadsheet as a macro-enabled workbook, then open the VBA editor with alt+f11. Hit 'insert'; 'module'; paste the code below and save. You can now enter the current date using ctrl+d and the current time with ctrl+t.

Sub ins_date() ' ' insdate Macro ' ' Keyboard Shortcut: Ctrl+d ' ActiveCell.FormulaR1C1 = "=TODAY()" ActiveCell.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False Application.CutCopyMode = False

End Sub Sub ins_time() ' ' ins_time Macro ' ' Keyboard Shortcut: Ctrl+t ' ActiveCell.Value = Time

End Sub