Adding a year check to a "starts this week" formula
Hi - I have a formula that I use in my sheets that notes if a task starts this week, next week or should have started last week.
=IF(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY())), "Starts This Week", IF(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY()) + 1), "Starts Next Week", IF(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY()) - 1), "Started Last Week", "")))
I am having a hard time because this does not do a year check. I tried to add the year check and got stuck. Is there somebody that could help me include a YEAR(TODAY()) = YEAR([Start Date]@row) into the formula? I've tried and given up.
Thanks in advance!
Best Answer
-
Hey @mgomez123
I used an IF/AND
=IF(AND(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY())), YEAR([Start Date]@row) = YEAR(TODAY())), "Starts This Week", IF(AND(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY()) + 1), YEAR([Start Date]@row) = YEAR(TODAY())), "Starts Next Week", IF(AND(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY()) - 1), YEAR([Start Date]@row) = YEAR(TODAY())), "Started Last Week", "")))
cheers
Kelly
Answers
-
Hey @mgomez123
I used an IF/AND
=IF(AND(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY())), YEAR([Start Date]@row) = YEAR(TODAY())), "Starts This Week", IF(AND(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY()) + 1), YEAR([Start Date]@row) = YEAR(TODAY())), "Starts Next Week", IF(AND(WEEKNUMBER([Start Date]@row) = (WEEKNUMBER(TODAY()) - 1), YEAR([Start Date]@row) = YEAR(TODAY())), "Started Last Week", "")))
cheers
Kelly
-
You are awesome -- thank you! Works perfectly!
-
Out of curiosity... Did this work as expected on the last and first weeks of the year? It seems like since the year won't equal the year of TODAY() it won't fit any of the conditions for the AND statements and would end up outputting a blank?
What if we just referenced the week number of today minus 7 days or today plus 7 days?
=IF(WEEKNUMBER([Start Date]@row) = WEEKNUMBER(TODAY()), "Starts This Week", IF(WEEKNUMBER([Start Date]@row) = WEEKNUMBER(TODAY(7)), "Starts Next Week", IF(WEEKNUMBER([Start Date]@row) = WEEKNUMBER(TODAY(-7)), "Started Last Week")))
Since we are basing it on today's date and moving backwards or forwards by a number of days, the year shouldn't matter. We don't have to worry about being in Week 52 and the next week is 1 (with unmatched years) or if we are in week 1 and the previous week is 52 (again with mismatched years).
-
Hey Paul
I'm sure you're right but didn't hear anything back. A few weeks ago someone asked if they was a way to check if the day was the last day of the month. I approached it by looking to see if the date+1 day caused a month number to change. I think, if needed, one could do this - date + 7 - to see where you are in the weeks.
How would you approach it?
Kelly
-
@Kelly Moore Are you able to provide a link to the thread for some context?
Help Article Resources
Categories
Check out the Formula Handbook template!
