WEEKNUMBER returning incorrect value?
[Please note dates are in YYYYMMDD format as I am in Australia and date column name is TueDate]
 Ignore last column they are calculated values that I needed to keep in the image so you could see the formula
I have the following sheet, which uses the WEEKNUMBER function to just return the number of the week in the year. The second column uses YEAR(TueDate@row) to return the year.
I am looking at every Tuesday and what week number that is. As it turns out 1/1/2019 is also a Tuesday and that correctly returned WEEKNUMBER=1 for 2019, but for Dec 31, 2019 it also returns WEEKNUMBER=1 for 2019.
At the end of 2020 it (correctly?) returns WEEKNUMBER=53 for Dec 29, 2020
Is this a bug in SmartSheet, Dec 31, 2019 cannot be week 1 for 2019?
Are there any suggested work arounds other than manually changing the values for Dec 31, 2019 and adjusting the following formulas by one?
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Answers

Hi Frank,
Smartsheet calculates week numbers based on Mondays. Due to this, the first day of the year may not be in the first week of the year. For example, if January 1st is a Monday, WEEKNUMBER returns 1, however if it’s a Friday, WEEKNUMBER returns 52.
When you have a moment, please submit a Product Enhancement Request to let our Product team know that you'd like to have this function work differently in Smartsheet.

Thanks for clarifying the day of the week it is calculated on.
I do not think it is the issue of calculation being a based on a Monday.
Dec 30, 2019 is also returning week 1 or 2019, it should be week 53 just as Dec 29, 2020 does.
This is causing some real issues as there are now two weeks in 2019 in week number 1, which is giving me incorrect totals in my weekly calculations.
I'm not sure what the correct solution is here, but it is a interesting edge case.
I will have to come up with another way of doing my calculations.
For interest, here is what is returned for the date ranges around the first day of the year:
20192020
51 2019 20191222
52 2019 20191223
52 2019 20191224
52 2019 20191225
52 2019 20191226
52 2019 20191227
52 2019 20191228
52 2019 20191229
1 2019 20191230
1 2019 20191231  1st week of 2019?
1 2020 20200101  1st week of 2020
1 2020 20200102
1 2020 20200103
1 2020 20200104
1 2020 20200105
20202021
52 2020 20201222
52 2020 20201223
52 2020 20201224
52 2020 20201225
52 2020 20201226
52 2020 20201227
53 2020 20201228
53 2020 20201229
53 2020 20201230
53 2020 20201231  53rd week of 2020
53 2021 20210101  53rd week of 2021?
53 2021 20210102
53 2021 20210103
1 2021 20210104
1 2021 20210105
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Hello
I have run into the same problem. I understand that the function calculates based upon the Monday of the week.
I am working in October  December 2020.
However, there are some weeks it returns week number +1. In weeks before October it seems to return the correct week number. And in the last week of December it returns 53. Which should never be so according to the function spec.
I am interested in the fix please.
Help Article Resources
Categories
Check out the Formula Handbook template!