Nested IF formulas for status
I am trying to write a nested IF formula, but as I run it there is one argument that is not resulting as I expected.
Basically what I want my Status column to represent is the following
-Done: when a project is in 100% compliance
-In Progress: when a project is anywhere from 1%-75% and its within the due date (the compliance % column is a Single select-Drowpdown list with options 1%, 25%, 50%, 75%, 100%)
-Not started: when a project is in 1% compliance but within the due date
-Delayed: when a project is not finished and the due date has expired OR when a project hasn't been started and today (as in any given day the report is consulted) is in less than 10 days from the due date.
I added the ¨Expected Result¨ column for a better appreciation of where I'm trying to get at
This is the nested formula that I am using:
=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row = 0.01, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))
I hope I was sufficiently clear and that you can help me,
Thanks!!
Answers
-
Hi @Zulma PH ,
I think I see the problem. Your "delayed" portion of the formula is currently checking for equal to 0.01.
=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row = 0.01, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))
The row you have indicated that should be displaying Delayed is showing Not Started because it is not equal to 1%. Perhaps you want to change that portion of the formula to reflect >=0.01
Let me know if that works for you!
Best,
Heather
-
Thank you, but that part of the formula is actually what I need to establish to comply with the second part of this statement:
--Delayed: when a project is not finished and the due date has expired OR when a project hasn't been started and today (as in any given day the report is consulted) is in less than 10 days from the due date.
-
@Zulma PH , could you possibly use this, then?
=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row <1, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))
That way it's indicating that it is less than 100% done, but doesn't necessarily have to be equal to 1%.
-
Thank you for your support Heather, that didn't quite do the trick, because the percentage did have to be 1% for that rule to apply, how ever it made me wonder if the priority of my rules was the problem and I could solve it with this formula using an OR function for the Delayed part :
=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due date]@row > TODAY()), "In Progress", IF(OR(AND([Compliance (%)]@row = 0.01, TODAY() >= [Due date]@row - 10), AND([Compliance (%)]@row < 1, [Due date]@row < TODAY())), "Delayed", "Not Started")))
-
@Zulma PH Great thinking! Did that work for you?
-
Yes! I tried it with all my projects now and haven't had any issues
Thank you!
-
Fantastic! Glad you found something that worked.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
