Smartsheet Formula (AND)

mragans23

I am looking to have a status column show either

-Overdue

-Complete

-In Future

-Not Required

I have everything working except the Not Required.

=IF(AND([Skills Assessment | Due Date (Y2)]@row < TODAY(), [Skills Assessment | Date Completed (Y2)]@row = ""), "Overdue", IF(AND([Skills Assessment | Due Date (Y2)]@row > TODAY(), [Skills Assessment | Date Completed (Y2)]@row = ""), "In Future", IF([Skills Assessment | Due Date (Y2)]@row > [Skills Assessment | Date Completed (Y2)]@row, "Complete", "Not Required")))

Sarah Keortge

I believe it's because your formula is set up to test for dates and so those rows are failing on the date check criteria before it gets to the final if false step. Try reversing some of your nested IF statements so that you evaluate the items that don't rely on comparing dates first. You also will have an issue with your formula if the completed date is after the due date because your evaluation is expecting it to be earlier - I suggest simply evaluating if there's a completed date filled in.

=IF([Skills Assessment | Due Date (Y2)]@row = "Not Enrolled Yet", "Not Required", IF(ISDATE([Skills Assessment | Date Completed (Y2)]@row), "Complete", IF(AND([Skills Assessment | Due Date (Y2)]@row < TODAY(), [Skills Assessment | Date Completed (Y2)]@row = ""), "Overdue", IF(AND([Skills Assessment | Due Date (Y2)]@row > TODAY(), [Skills Assessment | Date Completed (Y2)]@row = ""), "In Future"))))