# 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.

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

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

• It worked! Thank you!

• Excellent! Happy to help! 👍️

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!