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")))))))))))))))))))))
Best 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
-
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.
-
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
-
What are your column names? Can you provide a screenshot of the formula in the sheet similar to the snippet below?
-
Of course!
-
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.
-
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:
-
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!
-
Help Article Resources
Categories
Check out the Formula Handbook template!
