Formula for Status for Upcoming vs In Future

mragans23

I am trying to auto update the status column based on the Due Date and Date Completed Columns.

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

Its working good, but what I need is to say anything in the next 90 days, shows status as Upcoming, anything after 90 days so in future.

Michelle Choate 2

IF(AND([Due Date]@row > TODAY(90), [Date Completed]@row = ""), "In Future", IF(AND([Due Date]@row < = TODAY(90), [Date Completed]@row = ""), "Upcoming"

Paul Newcome

You can actually get rid of the AND statements since you already specified what to do if the Date Completed is a date. Give this a try (also included the "Upcoming" requirement):

=IF([Due Date]@row = "Not Enrolled Yet", "Not Required", IF(ISDATE([Date Completed]@row), "Complete", IF([Due Date]@row < TODAY(), "Overdue", IF([Due Date]@row > TODAY(90), "Upcoming", IF([Due Date]@row > TODAY(), "In Future")))))

mragans23

Thank you so much. Now last question, I think.

If Due date row is BLANK, status should show as No Date

Paul Newcome

You would just start things off like this:

=IF([Due Date]@row = "", "No Date", IF([Due Date]@row = "Not Enrolled Yet", …………………………………………