Analyzing your RescueTime Data – Part 2: Hours/Day by Month

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
        ' 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


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:


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


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:


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

3 thoughts on “Analyzing your RescueTime Data – Part 2: Hours/Day by Month

  1. Cool! The XLS link, though, doesn’t work – and following your steps results in error when attempting to enter “=RescueTimeTotal(,,2009,,,A2)”. Could you please post updated link to the finished XLS so I can reverse engineer? Thank you in advance.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s