Incorrect WeekNumber or(??) Year when trying to return a 'Week Number of Year' calculation
I am trying to return "Week Number of Year" (I want two digits for the Week Number hence the workaround in the formula) and all my weeks start on a Monday.
I have used the following formula which works great until you get to Monday 30 Dec 2024 where it returns "01 of 2024" - clearly wrong as Monday 01 Jan 2024 should be the only one that returns "01 of 2024". Either it should be returning "53 of 2024" or "01 of 2025".
=RIGHT(100 + WEEKNUMBER([W/c date]@row), 2) + " of " + YEAR([W/c date]@row)
I understand why the formula is doing this with the year as I'm just extracting and returning the Year, but what are your suggestions on how to get an accurate 'Week Number of Year' output?
I'd looked at this discussion first but there's no acceptable response: https://community.smartsheet.com/discussion/65046/weeknumber-returning-incorrect-value
Answers
-
You can use an IF statement to add 1 to the year if the year of the date plus 6 days is greater than the year of the date.
=RIGHT(100 + WEEKNUMBER([W/c date]@row), 2) + " of " + (YEAR([W/c date]@row) + IF(YEAR([W/c date]@row + 6)> YEAR([W/c date]@row), 1, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 306 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!