RYG formula with a date reference

Ryan Murphy

Looking for formula help with RYG referencing a date column. I want a green ball if the expiration date is more than a year away, yellow ball for expiration between today and 365 days and red for any date in the past.

Paul Newcome

Try something like this...

=IF([Date Column]@row< TODAY(), "Red", IF([Date Column]@row< TODAY(365), "Yellow", "Green"))

Paul Newcome

Try this one...

=IF(ISDATE([Date Column]@row), IF([Date Column]@row< TODAY(), "Red", IF([Date Column]@row< TODAY(365), "Yellow", "Green")))

Basically what this does is uses an IF statement to say that IF the date column has a date, then run this other formula.

=IF(ISDATE([Date Column]@row), run original formula)

Then I just dropped your original formula into the section where it outputs for a true value for the IF.

Please don't forget to mark the most appropriate response as the "Accepted Answer" so that others can know a solution has been found. This lets other people with a similar issue know that they can find help on your post.

Paul Newcome

Ok. Toss this in and see how you feel about it (use text wrapping)...

="Red: " + COUNTIFS(CHILDREN(), "Red") + CHAR(10) + "Yellow: " + COUNTIFS(CHILDREN(), "Yellow") + CHAR(10) + "Green: " + COUNTIFS(CHILDREN(), "Green")

EDIT TO SHOW INTENDED RESULT:

It will look like this...

Red: #

Yellow: #

Green: #

Paul Newcome

Try something like this...

=IF([Date Column]@row< TODAY(), "Red", IF([Date Column]@row< TODAY(365), "Yellow", "Green"))

Ryan Murphy

Awesome!!! Thank you Paul. How do I leave blank if the date column is blank?

Paul Newcome

Try this one...

=IF(ISDATE([Date Column]@row), IF([Date Column]@row< TODAY(), "Red", IF([Date Column]@row< TODAY(365), "Yellow", "Green")))

Basically what this does is uses an IF statement to say that IF the date column has a date, then run this other formula.

=IF(ISDATE([Date Column]@row), run original formula)

Then I just dropped your original formula into the section where it outputs for a true value for the IF.

Please don't forget to mark the most appropriate response as the "Accepted Answer" so that others can know a solution has been found. This lets other people with a similar issue know that they can find help on your post.

Ryan Murphy

One more follow up.... is there a way to count each RYG in the parent row? To get a count of each color.

Paul Newcome

Yes. Where would it be displayed within the parent row(s) and how would you want it displayed?

Ryan Murphy

In either the status column where the RYG balls are or in its own extra/helper column. Ideally I'd like to know the count of each color.

Paul Newcome

Ok. Toss this in and see how you feel about it (use text wrapping)...

="Red: " + COUNTIFS(CHILDREN(), "Red") + CHAR(10) + "Yellow: " + COUNTIFS(CHILDREN(), "Yellow") + CHAR(10) + "Green: " + COUNTIFS(CHILDREN(), "Green")

EDIT TO SHOW INTENDED RESULT:

It will look like this...

Red: #

Yellow: #

Green: #

Ryan Murphy

Thank you for the HELP!!!!!!

Paul Newcome

Happy to help! 👍️

Keep in mind... The formula to count the different colors and display them in the parent rows will only work if you only have those two levels of hierarchy. If you have multiple levels, it will only work on the lowest level parent row. Another solution would be needed to have it roll up to all of the different parent/grandparent/etc levels.