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.
The above imitates the WEEKNUM() command, implemented in most spreadsheets, except on one count:
Updated on Feb.23, 2012:
Also thanks to Snezy and Anonymous for the two additional attempts at this. I've tested both of the forumlas you posted in the comments, but had some problems making them work. Snezy's seems to not be able to produce one digit week numbers, and Anonymous would display "week" in the output, but based on his input, I did make this formula, which does work both in Google Spreadsheet and Excel, but since it relies on the fairly undocumented TEXT() function, it may not work on all other Spreadsheets:
Pretty neat!
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.
Updated on Feb.23, 2012:
Also thanks to Snezy and Anonymous for the two additional attempts at this. I've tested both of the forumlas you posted in the comments, but had some problems making them work. Snezy's seems to not be able to produce one digit week numbers, and Anonymous would display "week" in the output, but based on his input, I did make this formula, which does work both in Google Spreadsheet and Excel, but since it relies on the fairly undocumented TEXT() function, it may not work on all other Spreadsheets:
=LEFT(TEXT(C5; "w d"); 2)
Pretty neat!
