Calculating week numbers in Google Spreadsheets Revisited

In my previous entry Calculating week numbers in Google Spreadsheets I explored and presented a way of retrieving the week number of a given date. A couple of weeks ago a guy named Frederik Claes send me the best bug report from a non-programmer that I've ever received. He pointed out an error in an edge-case date that wasn't included in the test suite. After having looked at the problem and read up on the problem I found two errors.

First: the algorithm

The algorithm I used didn't comply with the specifications and for some dates it would return an invalid week number. While it had the advantage of being easy to read and verbose it was obvious that it would have to be replaced. An improved algorithm is currently being discussed on the talk-page of the wikipedia article and after a couple of verifications I ended up reimplementing my script using it instead.

Second: Time zone conversion

This is a classic example of a Works On My Machine problem. It turns out (and for good reasons to) that Google Apps Scripts run in a (potentially) different timezone from the actual spreadsheet. This is a major problem when dealing with dates since what looks like 2009-01-01 in the spreadsheet could very well have been converted to 2008-12-31 when it reaches the script. While I haven't had the time to fully investigate the possible workarounds I think I've found an acceptable way of transforming the date within the script to it's proper timezone.

The script

I've sent the script to Google for review so hopefully it'll be available through the Apps Script gallery within a couple of days. Until then you can copy the script from this blog and insert it to your sheets.

// by Markus Olsson (http://www.freakcode.com). 
// See http://www.freakcode.com/projects/google-spreadsheet-week-numbers
// The script is licensed under the MIT License. Basically you're free to do whatever you want with it. Attribution not necessary but appreciated.

// Update 2011-05-02:
//   Now returns current week when no arguments are given.
//   Adding attempt to automatically convert non-date objects, see code
//   Using 2 space indentation since that seems to be the proper indentation level for apps scripts
//
// Update 2010-12-05: 
//   Using the algorithm suggested by DR J R Stockton on http://en.wikipedia.org/wiki/Talk:ISO_week_date#Algorithms and
//   some of the JS implementation provided by DR Stockton on http://www.merlyn.demon.co.uk/weekcalc.htm#JS.
//
//   This fixes the erroneous week numbers reported by Frederik Claes.
//
//   Also fixed proper conversion between spreadsheet and script time zones since scripts now have explicit 
//   time zones (they didn't when I first started this script).
//
// This method is provided as an easy to use extension to the function namespace of Google Spreadsheet. 
// Since it was written a couple of people have reported that it is possible to get the week number from a
// date cell without resorting to scripts formula:
//    =VALUE(MID(TEXT( A1; "yyw") ; 3 ;5)) 
// Use whichever method you prefer, I think =WeekNumber(A1) is more readable but that could be because of the
// work I've put in into this script ;)
//
function WeekNumber(date, inIsoStringFormat)
{
  // Allows this method to be called without arguments to return the
  // current week. Shorter version of =WEEKNUMBER(TODAY())
  if(arguments.length < 1)
    date = new Date();

  // For a short while when I first started writing this script date cells 
  // wasn't given as pure js date objects so you had to convert them first. This 
  // hasn't been an issue for me for ages but some comments on my blog suggests 
  // that explicitly convertering into a Date instance has helped them. Maybe they're
  // using old spreadsheets, maybe not. But I can't foresee that adding this extra 
  // safeguard should cause any major problems (famous last words).
  if(Object.prototype.toString.call(date) !== '[object Date]')
    date = new Date(date);

  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetTimeZone = activeSpreadsheet.getSpreadsheetTimeZone();  

  // Google apps will automatically convert the spreadsheet date into the timezone
  // under which the script is running, this will revert it to the spreadsheet date and
  // at the same time truncate hours, minutes and seconds.
  date = new Date( Utilities.formatDate(date, spreadsheetTimeZone, "MMM d, yyyy") );
    
  // Get the week day where 1 = Monday and 7 = Sunday
  var dayOfWeek = ((date.getDay() + 6) % 7) + 1;

  // Locate the nearest thursday
  date.setDate(date.getDate() + (4 - dayOfWeek));
  
  var jan1 = new Date(date.getFullYear(), 0, 1);

  // Calculate the number of days in between the nearest thursday and januari first of 
  // the same year as the nearest thursday.
  var deltaDays = Math.floor( (date.getTime() - jan1.getTime()) / (86400 * 1000) )

  var weekNumber = 1 + Math.floor(deltaDays / 7);
  
  if(inIsoStringFormat)
    return jan1.getFullYear() + "-W" + (weekNumber < 10 ? "0" + weekNumber : weekNumber);
        
  return weekNumber;
}

Licensing information