How do i write the following formula
I am trying to write a If-nested formulas that works out the following.
What i am trying to say with this formula is the following:
IF the status is Complete and the planned end date is today or within five working days = Green
IF the status is In Progress and the planned end date is today or with in three working days = Yellow
IF the status is Not Started and the planned end date is today or with in 10 days after todays date = Red
=If(OR(Status@row = "Complete", [due date]@row =< Today(-5)), "Green", IF(Status@row = "In Progress", [due date]@row < Today(-3)) "Yellow", IF(Status@row = "Not Started", [Planned Finishe] = Today(10)) "Red"
Is there a AND function in here, or is what i am trying to ask not correct.... Please help
Best Answer
-
You have a lot of missing cases. For example, what should the status be if it's 11+ days away but not started, etc.
Also, I'm not sure why the end date matters if it's Complete
I think what you want is (assumes only 3 possible statuses)
IF(Status@row = "Not Started", [due date] <= Today(10), "Red"),
IF(Status@row = "In Progress", [due date]@row < Today(3), "Yellow"),"Green")
Answers
-
You have a lot of missing cases. For example, what should the status be if it's 11+ days away but not started, etc.
Also, I'm not sure why the end date matters if it's Complete
I think what you want is (assumes only 3 possible statuses)
IF(Status@row = "Not Started", [due date] <= Today(10), "Red"),
IF(Status@row = "In Progress", [due date]@row < Today(3), "Yellow"),"Green")
-
To write for your exact criteria listed, you would use the AND function within your nested IF's. Wherever you have written out "and", you will use an AND function to combine those two criteria.
So to pull from the criteria you have listed in your formula...
AND(Status@row = "Complete", [Due Date]@row <= TODAY(-5)) --> "Green"
AND(Status@row = "In Progress", [Due Date]@row< TODAY(-3)) --> "Yellow"
AND(Status@row = "Not Started", [Due Date]@row = TODAY(10)) --> "Red"
=IF(AND(Status@row = "Complete", [Due Date]@row <= TODAY(-5)), "Green", IF(AND(Status@row = "In Progress", [Due Date]@row< TODAY(-3)), "Yellow", IF(AND(Status@row = "Not Started", [Due Date]@row = TODAY(10)), "Red")))
Keep in mind that the above may not work as expected. The criteria set forth in your formula does not match the criteria you have written out and both leave a lot of different scenarios unaccounted for. The above just shows the proper syntax for using the AND function within a nested IF.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!