Creating an IF formula based off two columns where it can group the data by a timeline logic

Trying to find a way to automate our sheet by using the IF formula to pull data from two columns [status (i.e Sent, Scheduled) and date column] to build a logic to pull a timeline (i.e today, tomorrow, next week, 2 weeks, done, TBD.) into a new column.

This is the formula I've built but isn't working, where did I go wrong?


=IFERROR(Status@row), Status = "Sent"), "12. Done", IF(Date@row) = " ", "11.TBD", IF(Date@row) < TODAY(), "0. Overdue",IF(Date@row = TODAY(), "1. Today",IF(Date@row) < TODAY(),"days"), "2. Tomorrow",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday"),"3. This week",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+1,"4. Next week",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+2,"5. Two weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+3,"6. Three weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+4,"7. Four weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+5,"8. Five weeks",IF(WEEKNUMBER(Date@row),"Monday")=WEEKNUMBER(TODAY(),"Monday")+6,"9. Six weeks",IF(WEEKNUMBER(Date@row),"Monday")>=WEEKNUMBER(TODAY(),"Monday")+7,"10. Seven or more weeks away",IF(YEAR(Date@row)>YEAR(TODAY()),IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+1,"4. Next week",IF(WEEK(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+2,"5. Two weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+3,"6. Three weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+4,"7. Four weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+5,"8. Five weeks",IF(WEEKNUMBER(Date@row),"Monday")+52=WEEKNUMBER(TODAY(),"Monday")+6,"9. Six weeks",IF(WEEKNUMBER(Date@row),"Monday")+52>=WEEKNUMBER(TODAY(),"Monday")+7,"10. Seven or more weeks away")))))))))))))))))))))

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    I found another closing parenthesis out of place. Ugh. Sorry.


    🤞🤞

    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "11. TBD", IF(Date@row < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away"))))))))))))

Answers

  • Becky Wilson
    Becky Wilson ✭✭✭✭✭

    I don't know the answer, but did you try building them one IF statement at a time? I find by doing this I can make sure I am good and find the point of failure.

  • Paul Newcome
    Paul Newcome Community Champion

    Try this:


    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "TBD", IF(Date@row) < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row) = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away")))))))))))

  • Thanks for taking the time to review, I gave that a try but got back #UNPARSEABLE

  • Paul Newcome
    Paul Newcome Community Champion

    What are your column names? Can you provide a screenshot of the formula in the sheet similar to the snippet below?



  • Paul Newcome
    Paul Newcome Community Champion

    And do you have a screenshot of the formula in the cell similar to how the formula is displayed in my screenshot?

  • I just recognized you created additional columns reflecting the timeline logic - may be a stupid question but would I need to make the additional columns with the timeline logic to get it to work? My assumption is no.

    I was hoping the results would pull where I have the column titled "Formula Test", it'll pull the results so I can use a report to group by that column.


  • Paul Newcome
    Paul Newcome Community Champion

    I didn't create any additional columns. The only two columns referenced in my formula are the Status column and the Date column.


    I believe the error may be coming from an extra closing parenthesis tucked in where it shouldn't be. Try this:

    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "11. TBD", IF(Date@row < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row) = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away")))))))))))

  • We're getting closer @Paul Newcome! It's pulling only for 12. Done, 11. TBD, and 0. Overdue but pulls incorrect argument for the remainder ones:


  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    I found another closing parenthesis out of place. Ugh. Sorry.


    🤞🤞

    =IF(Status@row = "Sent", "12. Done", IF(Date@row = "", "11. TBD", IF(Date@row < TODAY(), "0. Overdue", IF(Date@row = TODAY(), "1. Today", IF(Date@row = TODAY(1), "2. Tomorrow", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0), "3. This Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 7, "4. Next Week", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 14, "5. Two Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 21, "6. Three Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 28, "7. Four Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 35, "8. Five Weeks", IF(Date@row + (2 - WEEKDAY(Date@row)) - IF(WEEKDAY(Date@row) < 2, 7, 0) = (TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0)) + 42, "9. Six Weeks", "10. Seven or more weeks away"))))))))))))

  • @Paul Newcome That worked! Thank you so so so much!

  • Paul Newcome
    Paul Newcome Community Champion

    SUCCESS!!! Glad we were able to get it sorted.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!