Imbedded If Statements
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")
Best Answer
-
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".
Answers
-
Try this:
=IF([Approval Status]@row <> "Approved", "Rejected", IF([End Date]@row< TODAY(), "Expired", "Active"))
-
Paul,
I'm still getting Rejected even when I should be returning Active or Expired.
-
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?
-
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.
-
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, nice catch, it is working nicely. Have a good weekend.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!