Is there a formula for within 6 months?

Nicole Endsley

I'm trying to start a formula for this scenario, but am stuck.

I'd like to have the column have a green symbol if the date in another column is within the past 6 months. Once 6 months have passed, I want the symbol to turn red.

Example:

Column 1 Column 2

12/12/22. Green Symbol

4/5/21 Red Symbol

Thoughts?

Jeff Reisman

Be sure your [Column 1] is a date-type column and contains actual date values.

The formula works, except for the part to leave the RYG column blank if there's no data value. Below, I've fixed that with a new formula:

=IF(ISDATE([Column 1]@row), IF([Column 1]@row < TODAY(-180), "Red", IF([Column 1]@row >= TODAY(-180), "Green", "")), "")

Jeff Reisman

@Nicole Endsley

Column 2 should be a symbol column, choose red, yellow, green balls for the symbols type.

Try this:

=IF([Column 1]@row < TODAY(-180), "Red", IF(ISDATE([Column 1]@row, "Green", ""))

This checks if the date is older than 180 days ago (roughly 6 months,) and if so, sets the ball to red; if it's not older than 180 days, and it is a date value, set it to green; otherwise (if Column 1 is a non-date value or is blank) leave Column 2 blank.

Nicole Endsley

Beautiful formula! It said "invalid" though

Jeff Reisman

Be sure your [Column 1] is a date-type column and contains actual date values.

The formula works, except for the part to leave the RYG column blank if there's no data value. Below, I've fixed that with a new formula:

=IF(ISDATE([Column 1]@row), IF([Column 1]@row < TODAY(-180), "Red", IF([Column 1]@row >= TODAY(-180), "Green", "")), "")

Nicole Endsley

@Jeff Reisman Thank you so much! That worked! The screenshots were super helpful.