Incorrect WeekNumber or(??) Year when trying to return a 'Week Number of Year' calculation

AEForrest
AEForrest ✭✭✭
edited 03/11/24 in Formulas and Functions

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

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!