RYG Formulas using a date Range
Hello, I am working on a RYG Formula and I am wanting to have the Health Status turn "Yellow" if the End Date is more than 10 days away, but less than 30 and I am having no luck.
Best Answers
-
Part of the problem is actually the IF/AND right before the bold portion. The AND is not closed out. Give this a go...
=IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow")))))))
-
Try this...
=IF(ISDATE([Start Date]@row), IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(ISBLANK([Start Date]@row), "Blank", "Blank"))))))))))
Answers
-
=if(and([end date]@row > today() + 10, [end date]@row < today() +30),"yellow"
Give that a try and see if it works for you
-
It did not work for some reason. I would also like to include another part in the formula such as the below if possible:
=IF(AND(Status@row = "Not Started" combined with the IF(AND([End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow")
-
IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow")
I don't see any syntax errors. Can you tell me what the error was?
-
It is giving me the incorrect Argument Set Error.
-
I just tested the formula and it works in my sheet. Do you have the end date column formatted as a date column?
-
If it is not the column type, can you copy/paste the formula you are using directly from the sheet?
-
Hello All, So I just spoke with my team and we are wanting to actually incorporate this formula into a much bigger formula below. The formula below works EXCEPT for the bolded part.
I did double check and the End Date is a Date field and the Status Column is a drop down list.
=IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY(), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow"))))))))
-
Part of the problem is actually the IF/AND right before the bold portion. The AND is not closed out. Give this a go...
=IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow")))))))
-
It worked! Thank you!
-
Hello, I do have another question with this formula. We are now wanting the health to be blank if there is no start date and I am unable to figure out how (or even if) we can make this work.
Here is the formula:
=IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(ISBLANK([Start Date]@row), "Blank", "Blank")))))))))
-
Try this...
=IF(ISDATE([Start Date]@row), IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(ISBLANK([Start Date]@row), "Blank", "Blank"))))))))))
-
That worked! Thank you!
-
Happy to help! 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.
Help Article Resources
Categories
Check out the Formula Handbook template!