# 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:

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

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

1. Eric says:

Impressive! Thanks, I’ll use this right away! :)

2. PO says:

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.