WEEKNUM function not working correctly

tmd63

New Member
Joined
Feb 21, 2014
Messages
40
Office Version
  1. 2013
  2. 2003 or older
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi, Nope. I thought of that and attempted to use the function with ALL variations from 1 to 21.
 
Upvote 0
So on your machine:

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

returns 1? Thats weird. Its 53 on mine.
 
Upvote 0
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.
 
Upvote 0
(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).

 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top