I’ve been cycling on a stationary bike for a little over a month now. The bike itself has gizmos to record miles and speeds and stuff but I wanted to keep a record that was outside of the bike itself. Looked around for some software for this (of course) and found a bunch of software and online services that would fit the bill (of course) but decided to just go simple with a Google docs spreadsheet.

My spreadsheet has three columns that track the date I biked, how far I went, and how long it took me: Date, Miles, Time. That’s all I care about, for now. But from that I can derive data like my average speed (miles/time) and do a chart of my progress. But what I really want to know is how far I’ve gone in total, how far I’ve gone this week, and how far I’ve gone this month. And that’s when I discovered the magic that is the Google docs QUERY function. It is very cool. It basically lets you do something akin to a SQL select statement on your spreadsheet data. So here’s what I came up with to automatically calculate my total miles this week and this month (overall total is easily done with the built-in sum() function). The hairy stuff below are the contents of cells and you can copy and paste into your spreadsheet.

This week:

=query(A:B, “select sum(B) where A >= date ‘” & text(date(year(today()-weekday(today())+1),month(today()-weekday(today())+1), day(today()-weekday(today())+1)), “YYYY-MM-DD”) &  “’ and A <= date ‘” & text(date(year(today()-weekday(today())+7),month(today()-weekday(today())+7), day(today()-weekday(today())+7)), “YYYY-MM-DD”) & “’ label sum(B) ‘THIS WEEK " & text(date(year(today()-weekday(today())+1),month(today()-weekday(today())+1), day(today()-weekday(today())+1)), “M/D”) & “’”)

This month:

=query(A:B, “select sum(B) where A >= date ‘” & text(date(year(today()),month(today()), 1), “YYYY-MM-DD”) & “’ and A <= date ‘” & text(date(year(today()),month(today())+1, 1)-1, “YYYY-MM-DD”) & “’ label sum(B) ‘THIS MONTH " & upper(text(date(year(today()),month(today()), 1), “MMM”)) & “’”)

So let’s break down the “This week” query. The cell starts with =query() which tells the spreadsheet to calculate that function and put the result in this cell. A:B is the range for the query. In this case, every cell in columns A and B (where A has my dates and B has my miles). Then the query itself is structured like this:

select sum(B) where A >= date ‘XXX’ and A <= date ‘YYY’ label sum(B) ‘ZZZ’

I took out all of the date calculations so you can see it more clearly. It just says “get the sum of column B where column A is between two dates”. Pretty simple. The main trick is calculating the date ranges for the start and end of the current week and the start and end of the current month. And here’s how you do that. Hopefully you can see where the calculations below plug in above and follow along.

The current week starts on today()-weekday(today())+1. That is, “today - the day of the week + 1”. So if today is Monday Oct 24, then this code would calculate: Monday Oct 24 - 2 + 1. Which ends up being Sunday Oct 23. And of course the last day of the week is just 6 days after that: today()-weekday(today())+7. (This is just the first expression +6 where the +1 and +6 have been summed.)

“This month” works exactly the same way but with different dates. The current month starts on date(year(today()),month(today()), 1). That is, “a new date based on the current year, the current month, and use 1 for the day”. And the current month ends on date(year(today()),month(today())+1, 1)-1. That is, “a new date based on the 1st of the month after this one minus 1 day”. You can optimize this a little by using the eomonth() function (which calculates end of month dates)—but I like my way because I can see exactly what’s going on.