Formula for Status for Upcoming vs In Future

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.

Find more posts tagged with

Accepted answers

All comments

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", …………………………………………

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.