WEEKNUM function not working correctly

[tmd63]

I am trying to use the WEEKNUM function but it miss reports the week.

example cell function in B7 =WEEKNUM(B6,11)
using a date of 29/12/15 reports week 53 (Week 53 is December 28th 2015 to January 3rd 2016)
but put a date of 01/01/2016 reports week 1?? It is still part of week 53.
https://www.epochconverter.com/weeks/2015

Why is this function miss reporting the week numbers as Jan 4th 2015 is week 1 and NOT week 2.
The same happens in 2016 and 2017.

 

[Joe4]

It looks like it might handle it correctly if you use "21" instead of "11" in the second argument, i.e.
=WEEKNUM(B6,21)

See this post here: https://www.reddit.com/r/excel/comments/3v55jo/weeknum_numbers_are_wrong_for_2016/

 

[tmd63]

Hi, Nope. I thought of that and attempted to use the function with ALL variations from 1 to 21.

 

[steve the fish]

So on your machine:

=WEEKNUM("01/01/2016",21)

returns 1? Thats weird. Its 53 on mine.

 

[Joe4]

So on your machine:

=WEEKNUM("01/01/2016",21)

returns 1? Thats weird. Its 53 on mine.

Me too. If I have a data of January 1, 2016 in cell A1, and use the WEEKNUM function with option 21 on it, it is returning 53 for me as well.

 

[barry houdini]

(Week 53 is December 28th 2015 to January 3rd 2016)

If that's your definition then it looks like you are using "ISO week numbers", but in general WEEKNUM doesn't use that definition

WEEKNUM function will always return 1 for 1st January, for any year and for all second arguments except 21

Using =WEEKNUM(Date,21) is the same as using =ISOWEEKNUM(Date) and in that system week 1 starts on the first Monday on or after December 29th (and is always 7 days long)

Using any other second argument, the start day can change but the definition is the same - week 1 starts on 1st Jan and week 2 will start with the next week starting date (e.g. Monday for 2nd argument 11).

 

[tmd63]

Well, One thing to remember is that I have Excel 2003 at home with all the Toolpaks installed (including Analysis toolpak and VBA analysis tookpak), but WEEKNUM does not appear in those toolpaks. I started the worksheet at home and then transferred to my work PC with Office 2013 (which has WEEKNUM included).
This may have impacted on the functions operation.

 

[barry houdini]

If you are using Excel 2003 then WEEKNUM should be available with Analysis ToolPak add-in, but you only get two possible second arguments, 1 and 2, and as I stated in my last post neither of those will accommodate a week numbering system that has weeks that span two calendar years, like ISO week numbers.

If you want to get ISO week numbers you can use this formula which will work in Excel 2003 and all later versions

=INT((B6-WEEKDAY(B6,2)-DATE(YEAR(B6+4-WEEKDAY(B6,2)),1,4))/7)+2