If Formula with Dropdown conditions based on Dates

Ryan Jared

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?

Purnima Gore

Hi

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

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

Hi

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