Adding a year check to a "starts this week" formula

mgomez123
mgomez123
edited 01/12/23 in Formulas and Functions

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • You are awesome -- thank you! Works perfectly!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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).

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Kelly Moore Are you able to provide a link to the thread for some context?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!