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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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.

🤞🤞

CSalcedo

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

Paul Newcome

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.