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

CSalcedo

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

Paul Newcome

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

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

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

CSalcedo

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

Paul Newcome

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

CSalcedo

Of course!

Paul Newcome

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

CSalcedo

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

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

CSalcedo

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

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

CSalcedo

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

Paul Newcome

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