How to auto-populate my companies Fiscal Year week number if its a 52 week year?
My companies FY starts 8/1 and ends 7/30.
Currently I am using =ROUND(((Created@row - DATE(2021, 8, 1)) / 7) + 0.49, 0) which works perfectly but it shows 56 weeks instead of a 52 week FY.
Best Answer
-
I believe that error occurs because the [Create] column is a 'Created Date' column type. The WEEKNUMBER function is expecting a MM/DD/YY date without a HH:MM time value. My bad, I tested my formula with [Created} set to 'Date' column type. I also noticed my original formula was missing the WEEKNUMBER function around the second and third date values.
My solution for extracting just the date from the date/time value in [Created] is to add a [Just Date] helper column with 'Date' as the column type, and add a column formula of: "=Created@row ". Then change my original formula to
=IF((WEEKNUMBER([Just Date]@row) + 22) > 52, (WEEKNUMBER([Just Date]@row) + 22) - 52, WEEKNUMBER([Just Date]@row) + 22)
Hope this helps!
Answers
-
The function you're using is clever. However I think there may be a simpler solution. Are you familiar with the WEEKNUMBER function (https://help.smartsheet.com/function/weeknumber)? I'm thinking you could calculate the WEEKNUMBER then add or subtract an 'offset' to fit your FY dates.
=IF( (WEEKNUMBER(Created@row) + 22) > 52, (Created@row + 22) - 52, Created@row + 22)
This should return a week number within the right range.
-
Hi @SolutionSal
I had tried the formula you had given and I am getting an #invalid column value error.
-
I believe that error occurs because the [Create] column is a 'Created Date' column type. The WEEKNUMBER function is expecting a MM/DD/YY date without a HH:MM time value. My bad, I tested my formula with [Created} set to 'Date' column type. I also noticed my original formula was missing the WEEKNUMBER function around the second and third date values.
My solution for extracting just the date from the date/time value in [Created] is to add a [Just Date] helper column with 'Date' as the column type, and add a column formula of: "=Created@row ". Then change my original formula to
=IF((WEEKNUMBER([Just Date]@row) + 22) > 52, (WEEKNUMBER([Just Date]@row) + 22) - 52, WEEKNUMBER([Just Date]@row) + 22)
Hope this helps!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!