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!
Very useful! Thanks a lot for taking the time to post.
ReplyDeleteAnother 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
Here's another one:
ReplyDelete=LEFT(TEXT(B2; "'week ' w d"); 8)
Thanks a lot! This is just what I needed :)
ReplyDeleteThanks! Works perfectly!
ReplyDeleteAh, THANK YOU. Been looking for this exact thing for awhile.
ReplyDeleteThanks
ReplyDeleteTHANK YOU. you saved me SO MUCH worry!
ReplyDeleteThe left text function was very useful, however I was facing problems in my sheet coz single digit weeks contain a space after the digit. This was affecting the Index-Match formulas in the sheet. I used a variant of the formula:
ReplyDelete=left(text(C5,"w d"),(find(" ",text(C5,"w d"))-1))
Works now!
@Alpana Mandal It would probably be simpler for you to just use TRIM(), to remove witespaces.
ReplyDeleteNice write up - you can also do it using the MID function: http://spreadsheetpro.net/the-fastest-way-to-make-a-weeknumber-function-in-google-spreadsheet/
ReplyDeleteThanks, SpreadsheetPro.net,
ReplyDeleteYou are right, =MID(TEXT(C5,"yyww"), 3, 2) is working just as fine as LEFT (or RIGHT for that matter).
But I was surprised about the "ww" vs "w" for the week number in TEXT(). Thanks for showing me.
One day I really have to find and read the source for the TEXT() implementation, as it's really not well documented!
FSTEFF.
ReplyDeleteThanks. I used it, it worked, I'm happy.
Thank you!
ReplyDeleteWorked for me just fine!
5 years later and your post is still most useful! Thank you soooooo much! Best regards from me and my team in Asia! (Hope there's a way to generate month # and quarter # as well!)
ReplyDeleteThank you for your comment - I'm happy to know others can use my work.
ReplyDeleteMonth number is easily obtained using the =MONTH(B6) command, where in this case B6 should contain the data string.
Quarter number can be easily calculated from the month number: =roundup(MONTH(B6)/3)
Just one request, i need the formula to return the numbers in YYYY-WK'weeknumber' format. Example: 2014-Wk01or 2014-Wk10
ReplyDeleteAlso out of all the formulas mentioned above, only two return correct value for all scenarios others fail one day "29th December 2013".
The following formulas give correct representation:
> =INT((C5-DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3)+WEEKDAY(DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3))+5)/7)
> =INT(RIGHT(TEXT(C5-1,"yyw"),2))
Rest all formula return 29th December as a part of 1st week of 2014 which is not the case.
Hi Abhishek Kumar,
ReplyDeleteThank you for your comment. I'm very puzzled by your observations, as I never saw a problem with any of the ones I've mentioned and commented on here.
I'm especially puzzled, as only one fails for me on the date you mention - but it's one of those you mention is working for you! : =INT(RIGHT(TEXT(C5-1,"yyw"),2))
I will have to digg into this strangeness once I have more time.
What date format are you using? I use 29/12/2013
As for how to present the values in a YYYY-WK format, this formula should get you going:
=TEXT(B16;"yyyy")&"-Wk"&TEXT( ( LEFT(TEXT(B16; "w d"); 2) ) ;"00")
I added some spaces so you can easily see where the original weekday formula is located, so you can replace it if needed.
Hope this helps.
Hi again Abhishek Kumar,
ReplyDeleteTested a bit, and it seems Snezy's comment (from 2008!) is wrong in two places.
1. It's not working on the current days date, but is subtracting one from that date. Note the B19-1 in the below formula.
2. It's using "yww", which is (apparently) returning strange values in the range you mention. But "yyww" is working"
(3). It's using the INT() to get rid of the front position zero.
So instead of this:
=INT(RIGHT(TEXT(B19-1;"yww");2))
Use this:
=RIGHT(TEXT(B19;"yyww");2)
or this:
=INT(RIGHT(TEXT(B19;"yyww");2))
And regarding your specially formatted YYYY-WK format, try this:
=TEXT(B19;"yyyy") & "-Wk" & RIGHT(TEXT(B19;"yyww");2)
Best regards.
Dude, this just helped me. Thank you thank you!!!
ReplyDelete