This is part 2 of my series on performing a custom analysis of your RescueTime computer usage data.

By the end of this tutorial, you’ll be able to produce a chart showing how many hours/day you spent online for each month in 2009.

Here’s what the final product looks like for me:

This tutorial does not walk you through every little step in Excel, only the key parts. It assumes you know how to navigate around the software, use formulas, etc.

If you don’t want to see how to do it and simply want to download the final product, click here. Paste in your RescueTime CSV data into the sheet titled “Data” then check out the “Hours per Day per Month” sheet.

But if you do want to see how to do it, make sure you check out part 1 of this tutorial: please read it first. It shows you how to export your data using the RescueTime API and how to set yourself up for this analysis. This tutorial assumes you completed part 1, meaning that you:

a) Downloaded your RescueTime data and

b) Saved the CSV file as an Excel Macro-Enabled Workbook (*.xlsm)

Now on we go:

1) Rename the sheet that contains your RescueTime logs to “Data” so you can later reference it with code:

2) Add a new tab and title it “Hours per Day per Month” or something a bit more eloquent–the name of this sheet isn’t important; it’s just for your reference.

3) Now it’s time to write the code that’ll perform the analysis.

Open up Excel’s Visual Basic Editor by pressing Alt+F11. Right click on “Microsoft Excel Objects“, hover over “Insert“, and click “Module“. This is where the code is going to be.

4) Copy and paste the following code:

Option Explicit

Type TimeInfo
    Date As String
    WeekdayName As String
    Time As String
    Day As Integer
    Month As Integer
    Year As Integer
    Hour As Integer
    MonthName As String
    DateFormatted As String
End Type

Const DATETIME_COL As Integer = 1
Const TIMESPENT_COL As Integer = 2
Const ACTIVITY_COL As Integer = 4

'------------------------------------------------------------
'  Name    : GetTimeInfo
'  Purpose : Takes a time formatted like this: 2008-07-07T21:00:00
'            And converts it into information that you can quickly analyze
'------------------------------------------------------------
'
Function GetTimeInfo(sDateTimeStr As String) As TimeInfo
    Dim sDateArray() As String
    
    With GetTimeInfo
        .Date = Left(sDateTimeStr, InStr(sDateTimeStr, "T") - 1)
        .WeekdayName = WeekdayName(Weekday(.Date))
        
        .Time = Mid(sDateTimeStr, InStr(sDateTimeStr, "T") + 1)
        .Hour = Left(.Time, InStr(.Time, ":") - 1)
        
        sDateArray = Split(.Date, "-")
        .Year = Val(sDateArray(0))
        .Month = Val(sDateArray(1))
        .Day = Val(sDateArray(2))
        .MonthName = MonthName(.Month)
        
        .DateFormatted = .Month & "/" & .Day & "/" & .Year
    End With
    
End Function

'------------------------------------------------------------
'  Name    : RescueTimeTotal
'  Purpose : This function adds up the seconds for all the rows that
'            meets all of your criteria
'------------------------------------------------------------
'
Function RescueTimeTotal(Optional iDay As Integer = -1, Optional iMonth As Integer = -1, _
        Optional iYear As Long = -1, Optional iHour As Long = -1, Optional sWeekdayName As String = "", _
        Optional sMonthName As String = "", Optional sActivity As String = "") As Long

    Dim oDateInfo As TimeInfo
    Dim iSeconds As Long, bCountRow As Boolean
    Dim indexRow As Long, sThisActivity As String

    For indexRow = 2 To RescueTimeDataCount + 1
        oDateInfo = GetTimeInfo(Sheets("Data").Cells(indexRow, DATETIME_COL))
        iSeconds = Val(Sheets("Data").Cells(indexRow, TIMESPENT_COL))
        sThisActivity = Sheets("Data").Cells(indexRow, ACTIVITY_COL)
        
        bCountRow = True
        If iDay  -1 And oDateInfo.Day  iDay Then bCountRow = False
        If iMonth  -1 And oDateInfo.Month  iMonth Then bCountRow = False
        If iYear  -1 And oDateInfo.Year  iYear Then bCountRow = False
        If iHour  -1 And oDateInfo.Hour  iHour Then bCountRow = False
        If sWeekdayName  "" And oDateInfo.WeekdayName  sWeekdayName Then bCountRow = False
        If sMonthName  "" And oDateInfo.MonthName  sMonthName Then bCountRow = False
        If sActivity  "" And sThisActivity  sActivity Then bCountRow = False
        
        ' Only add the seconds if the row meets all the criteria
        If bCountRow Then RescueTimeTotal = RescueTimeTotal + iSeconds
        
    Next indexRow

End Function

'------------------------------------------------------------
'  Name    : RescueTimeDataCount
'  Purpose : Take advantage of the CountIf function to determine
'            how many rows of RescueTime data you have
'------------------------------------------------------------
'
Function RescueTimeDataCount() As Long
    RescueTimeDataCount = Application.WorksheetFunction.CountIf(Range("Data!A:A"), "") - 1
End Function

'------------------------------------------------------------
'  Name    : DaysInMonth
'  Purpose : Returns the # of days in a given month
'
'------------------------------------------------------------
'
Function DaysInMonth(sMonthName As String) As Integer
    
    Dim sLastTime As String, oTimeInfo As TimeInfo

    ' There's one small adjustment we need to make and that's for the last month of data
    ' If the logs end on September 21, you don't want to use 30 days in September because
    ' you only have 21 days of data
    
    sLastTime = Sheets("Data").Cells(RescueTimeDataCount - 1, DATETIME_COL)
    oTimeInfo = GetTimeInfo(sLastTime)
    If sMonthName = oTimeInfo.MonthName Then
        DaysInMonth = oTimeInfo.Day
    Else
        ' Otherwise, simply return the # of days in the month
        Select Case sMonthName
            Case "January":     DaysInMonth = 31
            Case "February":    DaysInMonth = 28
            Case "March":       DaysInMonth = 31
            Case "April":       DaysInMonth = 30
            Case "May":         DaysInMonth = 31
            Case "June":        DaysInMonth = 30
            Case "July":        DaysInMonth = 31
            Case "August":      DaysInMonth = 31
            Case "September":   DaysInMonth = 30
            Case "October":     DaysInMonth = 31
            Case "November":    DaysInMonth = 30
            Case "December":    DaysInMonth = 31
        End Select
    End If
    
End Function

The programmers out there will be able to follow it fairly easily and if it doesn’t make any sense to you, a brief explanation isn’t really going to help anyway. :)

5)  Back in “Hours per Day per Month” create a column called “Month” that lists all the month names.

Title Column B “Seconds” and in Cell B2 type

=RescueTimeTotal(,,2009,,,A2)

The order for the RescueTimeTotal function is

month, day, year, hour, weekday name, month name, activity

All of the parameters are optional, but you must include a comma even if you aren’t using a specific item and you want to include a parameter that comes after it.

So, for example, if you wanted to total up the # of seconds on Wednesday’s in January 2009 you could use:

=RescueTimeTotal(,,2009,,"Wednesday","January")

And the # of seconds you spent on HackerNews this year:

=RescueTimeTotal(,,2009,,,,"news.ycombinator.com")

Clear as mud?

Hit enter and if all went well, Excel should total the number of seconds you spent on the computer in January 2009.

6) Fill in the rest of Column B.

7) I recommend making Column C “Minutes”, which simply equals the seconds divided by 3600.

8) Column D title “Days in Month” and set D2 equal to:

=DaysInMonth(A2)

Do this for all the cells in that column.

9) At this point you should have the # of hours you spent on the computer in each month as well as the # of days in that month. Title Column E “Hours/Day” and set the cells equal to the hours from Column C divided by the days in Column D.

10) Tidy things up, add a chart, some style, and voila, you should have how many hours you spend on the computer per day per month:

If while going through this you come across anything that’s unclear or incorrect, please drop me a comment below and let me know so I can update it.

Next up: Calculating the % of time you spend online, per month

I spend a lot of time on the computer: 8-9 hours/day at my day job and another few at night and on weekends preparing for a startup.

Ironically, despite an proclivity for analysis and plenty of data to analyze, I’ve never taken the time to see exactly where all those hours go. I don’t have any data for my day job, but I do have RescueTime installed at home on both my Macbook and my PC, which has been busily recording my computer usage since I first installed it in July 2008.

I’d like to consider myself a pretty productive programmer, but before I embark on a new project, I wanted to see if there was anything I could do to improve. I spent the last several days going over this data and the results were, well, stunning. My reactions can be broken down into two broad categories: “Holy shit I spend a lot of time on the computer” and “Holy shit I waste a lot of time.” And so over the next several posts, I’ll be walking though an analysis of my 2009 RescueTime data as well as explaining, with code, how you can do the same.

Getting Started

RescueTime, for the unenlightened, is a free program that quietly logs information about what software you use and how much time you spend using it. The RescueTime software uploads that data to rescuetime.com, which then displays it for you in a variety of nifty visualizations.

Here, for example, is a breakdown of my January 2009 Activities, as seen on my RescueTime.com Dashboard:

There’s a few other visualizations too: efficiency summary, activities by day, breakdown by category, and a productivity analysis.

My only complaint about RescueTime is that there are not enough visualizations. For example: I want to see how much time I spend on the computer per week over the last year–how do I do it? How about average computer use per day of the week? Broken down by hour on each of those days? By month, how much time do I spend on websites? These are actionable metrics, but unless you do your own analysis or are prepared to navigate through dozens of different time periods on the dashboard, it’s not easy to get. My 2c: Have a separate page  with links to dozens of user-requested visualizations. Not everyone will want it, but it’ll be there for those that do. Could be a pro feature too.

To do your own analysis, you’ll first need to sign up for a Pro account so that you can download the data, which costs $8/mo.

Next, download your usage data by using the RescueTime API. Once you have an API key, simply go to the following URL to download your usage history:

https://www.rescuetime.com/anapi/data?rtapi_key=yourAPIkey&perspective=interval&format=csv&resolution_time=hour&restrict_kind=activity&restrict_begin=2009-01-01&restrict_end=2009-12-31

There’s plenty of ways to customize the output per their API Documentation, but I found the one above gets me everything I need: organized by time and activity, formatted as a comma separated value file, broken down by hour, and spanning all of 2009.

Here’s what the file looks like:

We’ll use this Excel file to do all of the future analysis. When you save it, save it as a “Excel Macro-Enabled Workbook (*.xlsm)”, which will let you write macros to analyze the data.

For a quick example, you can total Column B, Time Spent, and divide by 3,600 to calculate the number of hours you spent on the computer. For me, that works out to be a whopping 3,018,243 seconds, or 997.52 hours. (You can also get this by clicking ‘year’ next to ‘show current’ at the top of the RescueTime dashboard).

Next post: How to calculate the average hours you spend per day on the computer broken down by month.

If I had 20 days to solve a problem, I would take 19 days to define it.

— Albert Einstein

I’m taking a break this month in order to brainstorm and figure out what to work on next.

I hope to start hacking away again sometime during the next few weeks and honestly I can’t wait; the idleness is driving me crazy ;)