What is the formula for This week, Next week, etc.

Jerry Alexander

I had formulas to check one of 5 checkbox columns if a specified date (Projected Start) fell within "Last week, this week, next week, this month, next month" based on todays date. (see attached)

=IFERROR(IF(WEEKDAY([Projected Start]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Projected Start]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Projected Start]@row + 1)), 1), IF(WEEKDAY([Projected Start]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Projected Start]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Projected Start]@row)), 1))), "")

As you can see in the screenshot, its not working. Can this formula be saved?

Capture.PNG

Paul McGuinness

Hi @Jerry Alexander

Without digging too deeply try pushing the +1 element outside of the date brackets.

so instead of WEEKNUMBER([Projected Start]@row + 1)) try WEEKNUMBER([Projected Start]@row) + 1)

=IFERROR(IF(WEEKDAY([Projected Start]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Projected Start]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Projected Start]@row + 1)), 1), IF(WEEKDAY([Projected Start]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Projected Start]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Projected Start]@row)), 1))), "")

Did that fix it?

Hope that helps

Thanks

Paul

Jerry Alexander

Thanks Paul, no, I'm still getting the same result.

Hollie Green

If I am understanding correctly I think it is the WEEKDAY part of your formula. The way your If formula is written it will only do the true value if the day of the week of the date in Projected Start is a Sunday I would try

This formula would be for last week

=If(YEAR([Projected Start date]@row=YEAR(TODAY(-7)),IF(WEEKNUMBER[Projected Start date]@row=WEEKNUMBER(TODAY(-7)),1,""),"")

This formula would be for this week

=If(YEAR([Projected Start date]@row=YEAR(TODAY()),IF(WEEKNUMBER[Projected Start date]@row=WEEKNUMBER(TODAY()),1,""),"")

This formula would be for next week

=If(YEAR([Projected Start date]@row=YEAR(TODAY(7)),IF(WEEKNUMBER[Projected Start date]@row=WEEKNUMBER(TODAY(7)),1,""),"")

This formula would be fore this Month

=IF(YEAR(Projected Start date]@row=Year(Today()),IF(MONTH([Projected Start date]@row=MONTH(Today()),1,""),"")

This formula would be for Next Month

=IF(YEAR(Projected Start date]@row=Year(Today()),IF(MONTH([Projected Start date]@row=MONTH(Today()+1),1,""),"")