# RYG formula with Open date

✭✭✭

Hi Everyone - I'm looking to used RYG for project health, Comparing Requested Completion date with Actual Finish Date. The formula works - except when there is no date entered into "Actual Finish Date".

=IF([Actual Finish Date]@row <= [Requested Completion Date]@row, "Green", IF([Actual Finish Date]@row > [Requested Completion Date]@row, "Red", 0))

Thank you

• ✭✭✭✭✭✭

Basically we want to say

=IF(appropriate date > [Requested Completion Date]@row, "Red", "Green")

Then we use an IF statement to output the "appropriate date". In this case we say

"IF the [Actual Finish Date]@row is not blank, then use the [Actual Finish Date]@row, otherwise use TODAY()."

• ✭✭✭✭✭✭

What outcome are you expecting in this circumstance? And will it differ if the requested completed date is older than today vs. if it is in the future?

• ✭✭✭

If the Actual Finish date or Today is past the Requested date - it should be red

If the Actual Finish date or Today is not past the Requested date - it should be green

• ✭✭✭✭✭✭

Give this a try:

=IF(IF([Actual Finish Date]@row <> "", [Actual Finish Date]@row, TODAY()) > [Requested Completion Date]@row, "Red", "Green")

• ✭✭✭

Thank you, Paul! That worked!

• ✭✭✭

@Paul Newcome - Can you explain the Bold section? What is the formula doing?

=IF(IF([Actual Finish Date]@row <> "", [Actual Finish Date]@row, TODAY()) > [Requested Completion Date]@row, "Red", "Green")

• ✭✭✭✭✭✭

Basically we want to say

=IF(appropriate date > [Requested Completion Date]@row, "Red", "Green")

Then we use an IF statement to output the "appropriate date". In this case we say

"IF the [Actual Finish Date]@row is not blank, then use the [Actual Finish Date]@row, otherwise use TODAY()."

• ✭✭✭

@Paul Newcome - Thank You!

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!