January 20, 2010

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!

weeknumbers-in-google-apps-script-gallery

Licensing information

11 comments:

  1. You need to add a "WeekNumberYear".

    ReplyDelete
  2. 12/28/2009 to 12/30/2009 gives week 1, should be 53. Calculation result examples:
    2009-12-27 -> 52 (ok)
    2009-12-28 -> 1 (wrong)
    2009-12-29 -> 1 (wrong)
    2009-12-30 -> 1 (wrong)
    2009-12-31 -> 53 (ok)
    2010-01-01 -> 53 (ok)
    2010-01-02 -> 53 (ok)
    2010-01-03 -> 53 (ok)
    2010-01-04 -> 1 (ok)

    ReplyDelete
  3. Use, to extract the week number:
    B2: 2009-12-28
    C2: =VALUE(MID(TEXT( B2; "yyw") ; 3 ;5))


    Or format (using the GAS code SpreadsheetApp.getActiveRange().setNumberFormat("w"); ) selected cells with dates to only display the week number

    ReplyDelete
  4. by: www.alatbantusexterbaru.com
    Hello sir, after I read the article you get to the bottom, It is very interesting to look at, it can provide benefits, and also the lessons, I really like it.
    so that I can remember with this master page and be back here, I leave a trail so that I could track my trail I have ever visit
    alat bantu sex pria
    alat bantu sex wanita
    I apologize if I trace unfavorable.

    ReplyDelete
  5. The regular weeknum function already available in Google Sheets now supports a second argument `type`. If you supply it with the value of 21, the weeknum function returns ISO week numbers.

    https://support.google.com/docs/answer/3294949?hl=en

    ReplyDelete
  6. By; Viagra Asli
    Hello sir thank you, I've visited your blog and I have read the article from the beginning to the end, thank you sir I got a good lesson, may be useful for me, thank you sir hope your job will be a blessing.
    Alat Bantu Sex , Alat Bantu Sex Terbaru , Sex Toys Terbaru

    Best wishes

    ReplyDelete