RYG formula with Open date

Laura7

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

Paul Newcome

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

Nick Korna

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?

Laura7

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

Paul Newcome

Give this a try:

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

Laura7

Thank you, Paul! That worked!

Laura7

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

Paul Newcome

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

Laura7

@Paul Newcome - Thank You!

Paul Newcome

Happy to help. 👍️