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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!