WeekNumber Across Years

Kelly Dvorak

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?

John Jonassen

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.

Kelly Dvorak

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!!

John Jonassen

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.

Kelly Dvorak

Do you have both a due date and reporting date in the project plans/schedules?

John Jonassen

@Kelly Dvorak , I've not used the project component within SS as of yet.