December 5, 2010

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;
}

28 comments:

  1. I can appreciate this issue - it is one my product also tripped over a few years ago - similar issue - the consumer and the server side code are often not in the same TZ - big problem! Thanks for the function - I needed it today - and for 1/1/11 what was published gave me '52' - so I'll see if this script resolves the issue.

    ReplyDelete
  2. I still get 52 for 1/1/11.

    ReplyDelete
  3. I fixed the script by changing:

    date = new Date( Utilities.formatDate(date, spreadsheetTimeZone, "MMM d, yyyy") );

    to

    date = new Date( Utilities.formatDate(new Date(date), spreadsheetTimeZone, "MMM d, yyyy") );

    ReplyDelete
  4. Thank you for writing this function. It works great in my GoogSheet and I agree it's more readable than the VALUE MID TEXT method. I hope that it gets added as a GFunction eventually. :-) Cheers.

    ReplyDelete
  5. Would really like to use this but I get "error: Service timed out: Apps Script" on many of my cells at the end of a 250 line spreadsheet. Any ideas?

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I guess these belong to the edge cases.

    Both the formula "=VALUE(MID(TEXT( C4; "yyw") ; 3 ;5))" and your script gives me week 1 for 2012-01-01, when it should be week 52.

    Have you tried cases of week 53?

    (Jag är också från Sverige.)

    ReplyDelete
  8. @winnberg: I've added 2012-01-01 to the test suite (https://spreadsheets.google.com/ccc?key=0AueEb10tSoSUdHBMTFlMbnJwTlAwX0JRTEhSd2hxQ3c&hl=en) and the script correctly returns w52. Are you sure you have the latest version? Also make sure to check what timezone your spreadsheet is configured in as this is taken into account by my script (File->Spreadsheet settings). If you still have problem please create a sheet illustrating the problem and share it with me and I'll have a look.

    ReplyDelete
  9. Thanks a LOT! I have searched for a way to use Googles formatDate(date, "GMT", "w") but it gives wrong weekday no matter what timezone you set in File/Spreadsheet setting. Your function works perfectly! :)

    ReplyDelete
  10. Hey Markus, thanks for that nice function! Now it would be really interesting to have a function for the other way around. Give it a CW and get the date and day of the Monday the week starts with. Or has Google Apps a built-in function for that I did not discover yet?

    ReplyDelete
  11. I've just 'borrowed' your code...your link to your license is 404. I will update my usage when the document is found.

    ReplyDelete
    Replies
    1. Hi Adrian.

      Sorry about the broken link, fixed it. The code is MIT-licensed, as in do pretty much whatever you want :)

      Delete
  12. I have tried to use the script in a spreadsheet containing the dates for an entire year. Because the script is called too many times(for each cell) error messages appear for some cells.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Hi, It worked perfect yesterday but now I get "error: Service timed out: Apps Script" all the time.
    Do you have any idea to avoid this?

    ReplyDelete
  15. Thank you! It worked.

    ReplyDelete
  16. Something seems to have gone wrong with your function as from week 13 2105 - seems to run for two weeks
    https://docs.google.com/spreadsheets/d/1tJVOC2muUZ6jsF99VziBG4IUhw3NDCBebU3G-Jx-1ag/edit?usp=sharing

    ReplyDelete
  17. Thanks Markus. It helped a lot. Appreciate your efforts on this and helping others.

    ReplyDelete