Weeknum() function

[Juha_Antero]

Hi everyone,
seems I do not know how to use the WEEKNUM() function since it seems to give me wrong numbers. And yes, this regardless of the additional parameter 1 or 2 (which accounts for the first day of week). More specifically: today we are living the week number 2 but WEEKNUM() function in Excel returns 3. Can anybody explain why and is there anywhere any cure for the ailment?
Of course there is the =WEEKNUM(now();2)-1 solution, but that is not what I am looking for...
Regards
Juha Antero

[Jacob Hilderbrand]

Today (1-10-05) is the third week of the year. If weeks start on Sunday then the first week was just one day (Saturday 1-1-05). And if the weeks start on Monday then the first week was just two days (Saturday 1-1-05 to Sunday 1-2-05).

If you want to count full 7 day weeks then try this.


=INT((TODAY()-DATE(2005,1,1))/7)

[Juha_Antero]

Hi Jacob,
actually we do start our year with the week having at least 4 days in Jan. So unfortunately there seems to be no internationally recognised standard for this (to my great surprise, since the EU seem to issue directives on things even smaller, eg. the bend of a banana or a cucumber and such all important matters). It really is week 2 in Finland whereas in Excel the week is 3.
Your esteemed solution results in 1 (1,45...)..

Regs
Juha

[Jacob Hilderbrand]

The formula was just for full 7 day weeks. So there has just been one week (and 3 days). If you want the current week you can try this.


=ROUNDUP((TODAY()-DATE(2005,1,1))/7,0)

[Juha_Antero]

Thanks,
this works OK as long as I change the date-part: DATE(YEAR(NOW());1;2) to start the week with a Monday..
Regs
JA

[Juha_Antero]

..to continue: try with 31.12.2004 which was supposed to be week 53 the above gives 52, so need to be careful, or ...

[Jacob Hilderbrand]

How about this?


=ROUNDUP((A1-DATE(YEAR(A1),1,1))/7,0)

Where A1 houses the date to check.

[Steiner]

You could check out this page, it gives some details on weeknumbers in Excel:

http://www.cpearson.com/excel/weeknum.htm

[Juha_Antero]

Brilliant, all the things I need.
Thanks a bunch.

[Ken Wright]

Just for reference, in case you were going to use it in an array formula, the WEEKNUM() function doesn't play nice in Arrays :-)

[Juha_Antero]

Thanks for that. I was just confused & surprised to learn that there are areas were Bill G does not provide for compliance with ISO...:-)

[Zack Barresse]

Maybe this will help ..

http://www.vbaexpress.com/forum/showthread.php?t=470