Help with a Formula

MClifton

Hello, I need help creating a formula within a cell.

If the Review Status is Approved AND the Original Workflow Session Date is today and/or in the past then the % complete should turn to 100%.

If the Review Status is Approved AND the Original Workflow Session Date is in the future the % Complete should stay at 0%.

If the Review Status is Member Review Needed, the Original Workflow Session Date doesn't matter, and the % Complete should stay at 0%.

If the Review Status is Follow-Up Needed, the Original Workflow Session Date doesn't matter, and the % Complete should stay at 0%.

If the Review Status is Executive Service Agreement, the Original Workflow Session Date doesn't matter, and the % Complete should change to 100%.

If the Review Status is Not Needed, the Original Workflow Session Date doesn't matter, and the % Complete should change to 100%.

The two different options in the % Complete section is 100% and 0%.

The image below shows the examples of status's, dates and % Complete:

I currently have this set as an Automation Workflow but would love if it could be a formula within the cell, if possible.

Thank you!

Paul Newcome

Try this:

=IF(OR([Review Status]@row = "Executive Service Agreement", AND([Review Status]@row = "Approved", [Original Workflow Session Date]@row<= TODAY(), [Original Workflow Session Date]@row <> "")), 1, 0)

Paul Newcome

Try this:

=IF(OR([Review Status]@row = "Executive Service Agreement", AND([Review Status]@row = "Approved", [Original Workflow Session Date]@row<= TODAY())), 1, 0)

MClifton

Hi Paul,

This formula works great for everything, but is still including blank date fields in the Original Workflow Session Date. Any suggestions for that? Thanks so much for your help.

Paul Newcome

What do you want to happen in if the date is blank? In reading the above, I see mentioned where the date doesn't matter in certain cases. Would you want it to remain blank if the date is blank regardless of the status?

MClifton

I think this is where I am running into the issues. It only matters for the Approved Review Status if the date is blank. So if the Review Status is Approved and the date is blank it should stay at 0%. Thanks again

Paul Newcome

Try this:

=IF(OR([Review Status]@row = "Executive Service Agreement", AND([Review Status]@row = "Approved", [Original Workflow Session Date]@row<= TODAY(), [Original Workflow Session Date]@row <> "")), 1, 0)