How can I combine two formulas to bring back more detailed results

I am currently using formula

=IF([Status]@row = “Complete”, “Complete”, IF([Due Date]@row < TODAY(), "Past Due", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "At Risk", "On Track")))

In an effort to populate the Overdue Status column on my Smartsheet with the status of Complete if the task row is marked as Complete and if not Complete, to call back a result of At Risk for tasks rows with a due date within the next 7 days or a result of Past Due for task rows with a Due date earlier than today.

The above formula is bringing back a result of #Unparseable.

The portion of the formula IF([Due Date]@row < TODAY(), "Past Due", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "At Risk", "On Track"))) works perfectly. It breaks when I try to add in the Complete designation.

I need to be able to exclude the Complete tasks from the Overdue Status column.

Find more posts tagged with

Accepted answers

All comments

Christian G.

Hi @Amber Holliday

~~I would start the nested if with the "If not complete " condition~~

=~~IF([Status ]@row<>"Completed",~~~~IF([Due Date]@row < TODAY(), "Past Due", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "At Risk", "On Track")))~~~~,"Completed")~~

Look at the quotation mark here:

=IF([Status]@row = “Complete”, “Complete”, IF([Due Date]@row < TODAY(), "Past Due", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "At Risk", "On Track")))

They are tilted. @Paul Newcome pointed that a multiple time in other formula. they are known as "Smart Quote" and Smartsheet can't process them. Try using notepad as a text editor to avoid getting the smart quote.

Amber Holliday

Correcting the "Smart Quote" quotation marks fixed me. Thank you so much! I would have never found that and have been working on this formula off and on for days. #lifesaver

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.