Formula for This week, Next week, etc.
Someone helped me with these, but I cant get them to work. Can someone let me know the error of my ways?
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,""),"")
Answers
-
Hi @Jerry Alexander,
You would probably want an IF/AND formula for these. For example, for the first one you would use:
=IF(AND(YEAR([Projected Start Date]@row) = YEAR(TODAY(-7)), WEEKNUMBER([Projected Start Date]@row) = WEEKNUMBER(TODAY(-7))), 1, "")
This shouldn't be too hard for you to modify to accomplish the other goals (changing the number after TODAY etc.).
Hope this helps, but if you've any problems/questions still then just post them up.
-
Fantastic, thank you. Can you help with (something I should know) adding the IFERROR to this formula?
-
Hi, @Jerry Alexander , the formula suggested by @Nick Korna works for all dates that occur within the same year.
If you need to account for when the Projected Start Date and the current date (TODAY) are in different years (e.g., last week of December and first week of January), then the following approach will work:
=IFERROR((YEAR(TODAY()) - YEAR([Projected Start Date]@row)) * 52 + WEEKNUMBER(TODAY()) - WEEKNUMBER([Projected Start Date]@row), "")
(YEAR(TODAY()) - YEAR([Projected Start Date]@row)) * 52 returns the number of weeks between the two years.
WEEKNUMBER(TODAY()) - WEEKNUMBER([Projected Start Date]@row) returns the difference between the two dates.
Added together the expression returns a positive number, "1", when the Projected Start Date was "last week". The formula returns a negative number when the Projected Start Date has yet to occur; "-1" when it's "next week". The formula returns "0" when both dates are within the same week.
Modify the formula for months (12 in a year) when using MONTH().
=IFERROR((YEAR(TODAY()) - YEAR([Projected Start Date]@row)) * 12 + MONTH(TODAY()) - MONTH([Projected Start Date]@row), "")
Help Article Resources
Categories
Check out the Formula Handbook template!
