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

Amber Holliday

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.

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