RYG Formulas using a date Range

Maria Watters

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.

Paul Newcome

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")))))))

Paul Newcome

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"))))))))))

L_123

=if(and([end date]@row > today() + 10, [end date]@row < today() +30),"yellow"

Give that a try and see if it works for you

Maria Watters

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")

L_123

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?

Maria Watters

It is giving me the incorrect Argument Set Error.

L_123

I just tested the formula and it works in my sheet. Do you have the end date column formatted as a date column?

Paul Newcome

If it is not the column type, can you copy/paste the formula you are using directly from the sheet?

Maria Watters

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"))))))))

Paul Newcome

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")))))))

Maria Watters

It worked! Thank you!

Paul Newcome

Excellent! Happy to help! 👍️

Maria Watters

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")))))))))

Paul Newcome

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"))))))))))

Maria Watters

That worked! Thank you!

Paul Newcome

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.