WEEKNUMBER returning incorrect value?

Frank Falco · 01/06/20

[Please note dates are in YYYY-MM-DD 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?

Alejandra · 01/07/20

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.

Frank Falco · 01/07/20

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:

2019-2020

51 2019 2019-12-22

52 2019 2019-12-23

52 2019 2019-12-24

52 2019 2019-12-25

52 2019 2019-12-26

52 2019 2019-12-27

52 2019 2019-12-28

52 2019 2019-12-29

1 2019 2019-12-30

1 2019 2019-12-31 - 1st week of 2019?

1 2020 2020-01-01 - 1st week of 2020

1 2020 2020-01-02

1 2020 2020-01-03

1 2020 2020-01-04

1 2020 2020-01-05

2020-2021

52 2020 2020-12-22

52 2020 2020-12-23

52 2020 2020-12-24

52 2020 2020-12-25

52 2020 2020-12-26

52 2020 2020-12-27

53 2020 2020-12-28

53 2020 2020-12-29

53 2020 2020-12-30

53 2020 2020-12-31 - 53rd week of 2020

53 2021 2021-01-01 - 53rd week of 2021?

53 2021 2021-01-02

53 2021 2021-01-03

1 2021 2021-01-04

1 2021 2021-01-05

Earl Tessmer · 10/20/20

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.

AEForrest · 03/11/24

https://community.smartsheet.com/discussion/comment/236312#Comment_236312

Hi all, this is still an ongoing issue and I don't think it's because Smartsheet calculates based on Mondays. I've posted a discussion here: https://community.smartsheet.com/discussion/118831/incorrect-year-when-trying-to-return-a-week-number-of-year-calculation#latest

WEEKNUMBER returning incorrect value?

Answers

Help Article Resources

Categories