Work Week Formula
Hello,
This may have already been asked however I am not seeing it in the threads, I am looking for a version of this formula =(WEEKNUMBER([Date Entered]@row)-5) to function properly so that it shows the work week (screenshot) :
Previously it worked without any issues and showed the 'Work Week', now it is giving me a -4:
Please let me know the error of my ways and why it is now not working.
Thank you!
Adriane
Best Answer
-
A simpler solution would be to deduct days from the date calculated in your WEEKNUMBER formula.
So =WEEKNUMBER([Date Entered]@row -35) will return the smartsheet week of 35 days before date entered...
Answers
-
I suspect that Smartsheet doesn't know how to subtract Weeks to a prior year. Since you're in Week 1 of 2021, it doesn't know how to subtract weeks back to 2020.
Is there a reason to subtract 5 from the Weeknumber formula?
If that is needed rather than just determining the actual week of the year, you'd likely need to add conditions to your formula to 1st check if the week number is for the 1st 5 weeks of a new year to say IF WeekNumber is 1, subtract 4 from 53, if 2, subtract 3 from 53 and so on. Once you move beyond that, it should function properly until you get to next year.
-
@Nic Larsen - thank you for responding, we are actually in week 49 of our fiscal year. However, after further review I found an old post and I think this formula did the trick.
=IF((WEEKNUMBER([Date Entered]@row) - 5) < 0, WEEKNUMBER([Date Entered]@row) + 48, WEEKNUMBER([Date Entered]@row) - 5)
Adriane
-
A simpler solution would be to deduct days from the date calculated in your WEEKNUMBER formula.
So =WEEKNUMBER([Date Entered]@row -35) will return the smartsheet week of 35 days before date entered...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!