freakcode: Calculating week numbers in Google Spreadsheets

I've fixed a couple of bugs and made some changes to the script. The code on this page is obsolete and only preserved for historical purposes. Please see my updated blog post for the updated code

I use an excellent little app called TimeTracker to keep track of how much time I spend at work. When I've collected too much overtime I try to compensate by taking a day off (unfortunately for me I haven’t exactly been great at balancing this in the past).

TimeTracker has an export feature that mails me a CSV file with all the data I need to produce a report suitable for showing to my boss should he ever complain about me not working enough.

I use the Google Spreadsheets to input and calculate how much I've been working. The problem is that I wanted to sum up how much I work in any given week but I couldn't find a function that, given a date, returns the ISO 8601 week number.

Writing it myself: The bad way

My first attempt at writing it myself was both messy and naive

=ROUNDDOWN((INT(B2-DATE(YEAR(B2);1;1))+(WEEKDAY(DATE(YEAR(B2);1;1);3)))/7)+1)

While this function has worked for me during the few months that I've been using it it won't work properly across new years eve, it doesn't take leap years into account and I'm sure there's a bunch of other things that's wrong with it.

But that was before I found out that it is possible to write Google spreadsheet functions in JavaScript! It's called Google Apps Script and it's JavaScript which you can write that gets executed server-side in the Google cloud.

Please note that unfortunately this only seems to be available to Google Apps users (i.e. not for standard Google accounts) at the moment. There seems to be some kind of a petition for opening up the scripts feature to the public that you might want to check out.

- As of 2010-03-11 Apps script is available to all docs users!

Writing it myself: The right way

The intertubes are littered with lots and lots of examples of how to calculate week numbers in JavaScript but very few of the nifty ones (the one-liners) seem to properly handle the 2010 transition correctly (2010-01-03 is week 53 and 2010-01-04 is week 1) so I decided to more or less translate the pseudo code at Wikipedia to JavaScript and ultimately Google Apps Script.

The solution is quite verbose and there's a couple of tricks that I haven't bothered implementing. But it works.

This code is obsolete and will fail for certain dates. It is only preserved for historical purposes. Please see my updated blog post for the updated code

// Built from pseudo code at:
// http://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date
// by Markus Olsson (http://www.freakcode.com). See http://blog.freakcode.com/2009/12/calculating-week-numbers-in-google.html.
function WeekNumber(inDate)
{
    function IsLeapYear(year)
    {
        return (year % 4 == 0) && ((year % 100 != 0) || (year % 400 == 0))
    }

    function OrdinalDays(inDate)
    {
        var ordinalDatesPerMonth = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334];
        var ordinalDatesPerMonthInLeapYear = [0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335];

        var ordinals = ordinalDatesPerMonth;

        if(IsLeapYear(inDate.getFullYear()))
            ordinals = ordinalDatesPerMonthInLeapYear;
  
        return ordinals[inDate.getMonth()] + inDate.getDate();
    }
      
    // Get the week day where 1 = Monday and 7 = Sunday
    var weekDay = ((inDate.getDay() + 6) % 7) + 1;
    var ordinal = OrdinalDays(inDate);

    var weekNum = Math.floor(((ordinal - weekDay) + 10) / 7);

    // If the week number equals 0, it means that the 
    // given date belongs to the preceding (week-based) year.
    if(weekNum == 0)
        return 53;

    // If 53 is obtained, we must check that the date is not actually 
    // in week 1 of the following year.
    if(weekNum == 53 && weekDay < 4)
        return 1;

    return weekNum;
}

I've made a small test suite available as a spreadsheet.

Trick

If you've got lots and lots of cells performing week number calculations you can select the cells, hit CTRL+C (or copy through the menu) then only paste the value (Edit menu -> Paste values only). There's really no need to calculate it over and over again since the likelihood of a major change to the ISO spec is pretty low ;)

Update 2010-01-20 (2010-W3): Google made some changes to Google Apps Scripts that broke the script. The sample above and the test suite has been updated.

Update 2010-03-11 (2010-W10): Google released the Google Apps Scripts gallery today and simultaneously enabled apps script for regular docs users. The apps gallery makes it possible to publish apps script! Great work by the Apps script team! I've submitted the week number script to the gallery and I'm currently waiting for them to approve it.

Update 2010-03-16 (2010-W11): Got an approval mail from Google, the script is now available in the script gallery!