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

January 8, 2010

Pretty automatically updating lighttpd server status or “mod_status on steroids”

At work we use the excellent lighttpd (or lighty) web server as a frontend reverse proxy that performs SSL offloading, and  some other neat tricks before our visitors reach our backend servers. It’s been working like a charm.

We’ve been using mod_status to keep track of how much traffic goes through our frontend server. mod_status is one of those little apps that gets the job done but not so much more.

server-status

It contains all the relevant information but I wanted to display this data on a separate monitor to as a way to keep track of how much traffic we where getting in real time.

Since it’s a static html page there wasn’t much interactivity there. So I whipped out my vim-editor and wrote a little helper page that dynamically polls data from the server-status page and displays it in a little more full-screen friendly way. You can see the result below (click on it to enlarge). It will automatically refresh the data once every 5 seconds.

server-status-pretty

Go ahead and download the source. It’s a single html file and all you have to do is to configure your lighty server so that the server status is enabled for a directory to which you’ve got write access to. Ours looks like this:

$SERVER["socket"] == "192.168.30.1:80" {
    status.status-url = "/server-status"
    server.document-root = "/var/www/status"
    mimetype.assign = ( ".html" => "text/html" )
    index-file.names = ( "pretty-status.html" )
}

Then you simply put the file in that page and it should just magically work. Note that the file loads the Google hosted jQuery library so if you don’t have access to the internet (on the machine your viewing from) you’ll have to download the jQuery script and place it along side the page.

Things on the todo list include pretty error messages when the data can’t be fetched and perhaps a nice little demo-mode.

[Download the source], [“Project page at freakcode.com”]

Licensing information