Fsteff: WEEKNUM() for Google Docs & Spreadsheets

I've been using Google Docs & Spreadsheets for some time now. Mostly the spreadsheets, though. During that time I've made some relative complex spreadsheets, and I'm really pleased with them.
However there still are a few normal spreadsheet commands missing, and yesterday I was in need for exactly one of them: WEEKNUM() (as detailed in ISO 8601:2000)

What I needed was to get the number of the week in a year (1 to 53) for any specific date.
After some time hacking at the keyboard, I got the following formula to work.
The date to check is located in cell C5.

=INT((C5-DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3)+WEEKDAY(DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3))+5)/7)

The above imitates the WEEKNUM() command, implemented in most spreadsheets, except on one count:

It's ONLY ISO 8601:2000, so there's no option to set the type.

Very usefull for me... perhaps usefull for you, too?

Thanks for the usefull comment, Piotrek. It's been fixed now.

Labels: Google Docs 'n' Spreadsheets

Posted by fsteff on Sunday, July 29, 2007 at 15:16 | Permalink

Very useful! Thanks a lot for taking the time to post.

Another solution I've seen is to use the poorly documented =TEXT formula like so:

=INT(RIGHT(TEXT(C5-1,"yyw"),2))

For a hint of some of the format codes available through the =TEXT function take a look at OpenOffice Calc help under "Number Format Codes". Some of the formats do not work in Google Spreadsheets, but it's a good starting point.

The only online place I found for the "Number Format Codes" help page is this:

http://plan-b-for-openoffice.org/ooo-help/r2.1/en-US/MAC/shared/01/05020301

Posted by Snezy | September 29, 2008 3:01 PM