Excel: WeekNum error in VBA and Formula Sheet

0
votes

For each calendar the number of week of days 01-02-03/jan/2016 is 53th weeks.

But in excel(VBA and Sheet Formula) this is the week nr. 1 of 2016.

 cellValue = "01/01/2016"
 res = WorksheetFunction.WeekNum(cellValue, 2)

This is serious mistake or correct ?

There is a patch for this problem ?

2
excelexcel-formulavba
The weeknumber of the days 01-02-03/jan/2016 is not 53 in each calendar. There are different definitions of week numbering. en.wikipedia.org/wiki/Week#Week_numbering - Axel Richter

2 Answers

3
votes

Looks like you're using the ISO definition of week number. If so then you'll need to change the line to:

res = DatePart("ww", d, vbMonday, vbFirstFourDays)

For a really good explanation of VBA for weeknum calculations, have a look at http://www.cpearson.com/excel/WeekNumbers.aspx.

BTW, the function =WEEKNUM() and WorksheetFunction.Weeknum() work differently in the Excel versions, so you might have more control with a pure VBA solution.

2
votes

You should be using 21 and not 2 as the second argument.