Name different weeks by IF(AND Formula and WEEKNUMBER.

Indira Buike

Hello! I need some help to make my simple formula more efective!

What I want is according to my [Start Date] know the week number, and depending on that number have another column saying if it's the current week, past 1 week, past 2 weeks, and past 3 weeks.

So my formula is this one

=IF([Week Number]@row = WEEKNUMBER(TODAY()), "Current Week", IF([Week Number]@row = WEEKNUMBER(TODAY()) - 1, "Past 1 Week", IF([Week Number]@row = WEEKNUMBER(TODAY()) - 2, "Past 2 Weeks", IF([Week Number]@row = WEEKNUMBER(TODAY()) - 3, "Past 3 Weeks", ""))))

And it works!

But I have 2 problems,

1) I'm using a helper column with the week number of my [Start Date] and I would like to avoid it if possible.

2) The formula works, but it won't show past 2, or 3 weeks because it was 2022 so it says that it's week 52, maybe I need to add the year in the formula but not sure how that works.

Can somebody please help me, and explain to me how to make it work and if I need to add the year for future formulas?

Thank you!!

Paul Newcome

I would suggest working in days alongside the week numbers.

Basically you want to compare the week number to the week number for 7 days prior to today. This will take care of any year overlap without having to actually build in anything for the years.

=IF([Week Number]@row = WEEKNUMBER(TODAY()), "Current Week", IF([Week Number]@row = WEEKNUMBER(TODAY(-7)), "Past 1 Week", IF([Week Number]@row = WEEKNUMBER(TODAY(-14)), "Past 2 Weeks", IF([Week Number]@row = WEEKNUMBER(TODAY(-21)), "Past 3 Weeks", ""))))