Calculating Previous X Weeks at the New Year Turnover

Jake Gustafson · 01/04/23

Hello, I've got this handy formula calculating if a row record is from the previous 5-weeks that worked great up until the new year. Now I need something to know that Week 1-2023 and Weeks 49-52-2022 are what should be considered. It's probably not as hard as I'm making it out to be and I'm sure someone has run into this in years past.

Here's what I have right (for a checkbox column):

=IF(AND(WEEKNUMBER(TODAY()) - [Week Number]@row < 6, WEEKNUMBER(TODAY()) - [Week Number]@row > 0), 1, 0)

Other suggestions on how anyone has gotten something like this to work for them would be helpful.

Thanks

Paul Newcome · 01/10/23

Ok. So to check the box in this most recent screenshot, we start by finding the current Monday.

=TODAY((2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) = 1, 7, 0))

Then we say this if the [Week Start Date]@row is greater than or equal to the current Monday minus 35 days (5 weeks) and less than or equal to the current Monday, then check the box.

=IF(AND([Week Start Date]@row>= TODAY((2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) = 1, 7, 0) - 35), [Week Start Date]@row<= TODAY((2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) = 1, 7, 0))), 1)

If you are using this for a filter or report then it isn't too big of a deal, but if you are trying to use the Week Number column to drive metrics, you could potentially run into issues where you have Week Number 1 in 2022 and Week Number 1 in 2023. This can be remedied by incorporating the year into whatever formulas you are using to calculate your metrics, but it is definitely something to keep in mind.

I personally try to get things into as few columns as I can though, so I would either use the dates or I would use a formula to generate a 6 digit number that will represent "yyyyww".

=VALUE(YEAR([Week Start Date]@row) + "" + IF(WEEKNUMBER([Week Start Date]@row)<10, "0", "") + WEEKNUMBER([Week Start Date]@row))

If you use that same concept on the source data sheet(s) then you can just compare this indicator instead of trying to manage both the week number and year as two separate variables in your formula(s).

Paul Newcome · 01/05/23

Have you tried using days instead of week numbers where you basically just flag rows that are within the past 35 days (5 weeks)?

Jake Gustafson · 01/05/23

@Paul Newcome no I have not done that. The particular instance here is on a summary sheet. I've got weekly summary rows pulling from the main data sheets and I only want to flag the current week and previous 4 weeks into a Report that is used on a Dashboard. So last week, it was great showing me weeks 48-52, but now this week I'm getting nothing.

I've got a Week Start Date field (which is the corresponding Monday). Then I calculate the Week Number off that date. If I go off the past 35 days, then my Week Start Date won't be included (i.e., 36 days old), but that week will need to be considered.

After a few weeks here this won't be an issue again until a year from now, but if there's some cleanup I can do now, that'll prepare me for the future and help me to avoid similar misfortunes in other sheets.

Paul Newcome · 01/05/23

So basically you want to go from the end of the current week back to the Monday of 5 weeks ago? Would the end of the current week be Sunday then?

Jake Gustafson · 01/05/23

Correct. Monday-Sunday weeks.

Paul Newcome · 01/05/23

Ok. So if you already have the current Monday in a field, we can add 6 to that for the end date of the date range and subtract 35 from that to get the start date of your date range.

=[Week Start Date]@row + 6

=[Week Start Date]@row - 35

If you just wanted to run it off of TODAY(), then you should be able to use this:

=TODAY((2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) = 1, 7, 0) + 6)

=TODAY((2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) = 1, 7, 0) -35)

Jake Gustafson · 01/05/23

Thanks Paul. Not exactly sure I followed, but I used your logic (I think) to put in a couple helper columns to get me closer to what I needed. At this point it's working as expected, but I'll keep an eye on it.

Helper 1:

=IF([Week Start Date]@row > (TODAY() - 35), 1, 0)

Helper 2:

=IF([Week Start Date]@row < (TODAY()), 1, 0)

5-Week Snapshot field:

=IF(SUM([Helper 1]@row, [Helper 2]@row) > 1, 1, 0)

Paul Newcome · 01/06/23

If [Week Start Date]@row is always a Monday, then we can add 6 to that date to get the Sunday (last day of that week). We can also subtract 35 from the [Week Start Date]@row to get the Monday (start of the week) from 5 weeks prior.

Anything between those two dates would be within that period you are looking for. You really shouldn't need helper columns.

Are you able to provide a screenshot for context, because I think I may be misunderstanding what exactly you are trying to do.

Jake Gustafson · 01/10/23

@Paul Newcome thanks for your patience. Here is some more detail.

Week Start Date is a hard coded value that'll always be a Monday.

Week Number is a formula based on the Week Start Date

The other field formulas are as indicated in my previous post.

There's a bunch of different data that I hid in between there that looks at the Week Number on the primary data sheets to summarize information, but the rolling 5-Week Snapshot keeps a more recent look into the performance for key stakeholders.

Let me know if you need anything else.

Paul Newcome · 01/10/23