Imbedded If Statements

JiMel

I'm trying to use a formula (below) that will:

1)If a deviation was Approved, it is past its End Date...if Past End Date return "Expired", if not return "Active"

2)If it was never Approved, return "Rejected"......no need to evaluate if it is past its End Date.

My formula is returning Rejected only.

=IF([Approval Status]@row = "Approved", IF(TODAY() > [End Date]@row, "Expired", "Active"), "Rejected")

Paul Newcome

It is because the formula is using "Approved" based on your original post, but the sheet is using "Approve" (without the "d" on the end). Try mine with "Approve" instead of "Approved".

Paul Newcome

Try this:

=IF([Approval Status]@row <> "Approved", "Rejected", IF([End Date]@row< TODAY(), "Expired", "Active"))

JiMel

Paul,

I'm still getting Rejected even when I should be returning Active or Expired.

Paul Newcome

Can you provide a screenshot of the rows that are "Rejected" that aren't supposed to be as well as a screenshot of the formula opened in a cell as if you are about to edit it?

JiMel

Paul, the formula of concern is in Current Status. I have my formula in row 1 and I pasted your formula in the rest of the rows. In the sitatution above, Current Status should be "Active" for Rows 1,3,4,6 and Rejected for Rows 2,5. At this time nothing meets the Expired.

Paul Newcome

It is because the formula is using "Approved" based on your original post, but the sheet is using "Approve" (without the "d" on the end). Try mine with "Approve" instead of "Approved".

JiMel

Paul, nice catch, it is working nicely. Have a good weekend.

Paul Newcome

Happy to help. 👍️