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.
The above imitates the WEEKNUM() command, implemented in most spreadsheets, except on one count:
Thanks for the usefull comment, Piotrek. It's been fixed now.
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.
Thanks for the usefull comment, Piotrek. It's been fixed now.
Labels: Google Docs 'n' Spreadsheets
