WeekNumber Across Years
I have been able to find success at using the weeknumber formula and combining that with a 'past due' vs 'due this week' vs 'due next week' indicator. I have a separate sheet that identifies what 'this week' vs 'next week' vs 'last week' is by week number so that I just need to change it in 1 place for all of my projects. Its been working great. Now that we're approaching January though, it no longer works. Anything in January is showing past due since the Week Number changes to 1. Any ideas on how to report something similar across years?
Answers
-
I have a build that requires multiple years, including a Reporting Week. What I have done is created another sheet that has all of the Saturday (Reporting Week Start) to Friday (Reporting Week End) ranges running vertically for 6 years, then apply the week number to that.
Then, in the sheets that need to get a hold of that week number, I perform a lookup formula to return that week number based on the Reporting Date.
-
Thanks Jon. I'm going to try something like this. I was hoping not to go down this avenue but if it works for you, its worth a shot! Thanks!!
-
It's a pain, I know...but the benefit is that I also use the same sheet within my Report Control workspace (where this is housed) across many sheets, so you'll have it built to help again I'm sure.
-
Do you have both a due date and reporting date in the project plans/schedules?
-
@Kelly Dvorak , I've not used the project component within SS as of yet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!