Weeknumber Formulas

lrmerlino88886

I have a formula set that checks a box if the weeknumber is within the last 8 weeks. At year end, I have to go in manually and uncheck and check the 8 weeks until we are in week 8 of the new year where I can reset the formula since the weeknumber formula does not look at weeks 45-52 as smaller than 1-8, and doesn't see them as past 8 weeks. Does anyone know of a workaround for this? Something that will take year end into consideration for last 8 weeks and last week with weeks 52 and week 1?

Paul H

Can you post an example of your formula so we can see where you are currently at?

lrmerlino88886

Sure I'm using this:

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

Referring to a weeknumber column that just has the weeknumber forumula in it.

Paul H

Try this I think i am close, it skips the weeknumber column and looks directly at the date@row

=IF(AND(WEEKNUMBER(TODAY()) - WEEKNUMBER(Date@row) > -52, WEEKNUMBER(TODAY()) - WEEKNUMBER(Date@row) < -45), 1, IF(AND(WEEKNUMBER(TODAY()) - WEEKNUMBER(Date@row) >= 0, WEEKNUMBER(TODAY()) - WEEKNUMBER(Date@row) < 9), 1, 0))

lrmerlino88886

OK. I can give it a try in the next couple of days. I don't have dates in my metrics sheets, just the source sheets. And right now I don't have checkboxes for weeknumbers in my source sheets. But I can always trigger my metrics sheets off of the checkbox in the source sheet...... It will take some rearranging to get it done. I should have time this week to do that and try it out. My metrics sheets are too large to allow for more cross sheet formulas, they are looking at a lot of data. Adding the year to the formula broke a lot of formulas and I had to rework things. So I think it would work better to put them in the source sheets. Thanks! I'll let ya know how it works.

Paul H

Here is using a weeknumber column, you might have to tweek depend if you count the current week as week 1, or last week as week 1

=IF(AND([Week Number]@row > -52, [Week Number]@row < -45), 1, IF(AND([Week Number]@row >= 0, [Week Number]@row < 9), 1, 0))

Kelly Moore

Hey @lrmerlino88886

Here's another approach and takes into account the year of the date to filter out old data, if any. It also compensates for the years that have more than 52 weeks (2023 is a 53 week year). If you want to add the +7 days to this formula to match yours, the formula should still work.

=IF(AND(WEEKNUMBER(TODAY()) <= 8, YEAR(DATE@row) = YEAR(TODAY()) - 1, (WEEKNUMBER(DATE(YEAR(TODAY()) - 1, 12, 31)) + WEEKNUMBER(TODAY()) - [Week Number]@row) <9), 1, IF(AND(YEAR(Date@row) = YEAR(TODAY()), Date@row <= TODAY(), WEEKNUMBER(TODAY()) - [Week Number]@row <9), 1))

Note there is a check to verify the Year of the entry, as well as a check that the date is in the past. You will need to edit to use your real Date column name.

Kelly