If Formula with Dropdown conditions based on Dates

I'm looking for a formula that could be integrated into a drop down field based on a past date.

I'm trying to have the "Status" column automatically flip to Off-Track if the date in the "Finish" column is now in the past and the "Status" column is not already in At Risk or Completed status.

I'm able to use =IF(Finish1 < TODAY(), "Off Track") but this is not taking in to account if the current "Status" column is not already in At Risk or Completed status. Does anyone know if there is a formula available for this?

Find more posts tagged with

Formulas

Accepted answers

Purnima Gore

Try something like this

=IF(Status@row = "Complete", "Complete", IF(AND(Finish@row < TODAY(), (OR(Status@row = "In Progress", Status@row = "Not Started"))), "Late", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), (OR(Status@row = "In Progress", Status@row = "Not Started"))), "Next 14 Days", "Future")))

I use this because card view is only available where you use dropdown and so this gives me this in a card view if I need it.

Hope that helps.

Kind regards

Purnima

All comments

Ramzi K

@Ryan Jared

Use and AND clause to combine your conditions:

=IF(AND(Finish1 < TODAY(), Status1 <> "At Risk", Status1 <> "Completed"), "Off Track")

I hope this helps,

Ramzi

Ryan Jared

Thank you for that!

If I could steal some more of your knowledge, one thing i'm struggling with is inputting the formula to the cell which has a drop down. Each row will start off as Not Started, and as the workflow goes on the status will manually change.

How can I pre-set the cell to have the formula and still stay active as the drop down items change.

Purnima Gore

Try something like this

I use this because card view is only available where you use dropdown and so this gives me this in a card view if I need it.

Hope that helps.

Kind regards

Purnima

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