Colored Dots Formula

birdie1087

I'm trying to use a formula to populate a column with colored dots based off of a date.

This is what I'm trying to do

Red - Date prior to TODAY
Yellow - Date within the next 90 days from TODAY
Green - Date greater than 90 days from TODAY
Gray - Date is blank

I keep getting the unparseable error.

This is my formula:

IF([Contract Expiration Date]@row < TODAY(0), "Red", IF([Contract Expiration Date]@row < TODAY(90), "Yellow", IF([Contract Expiration Date]@row ISDATE, "Gray", "Green")))

Carson Penticuff

Your ISDATE() syntax is a little off. I also reordered your conditions a little to avoid an error if something other than a date is entered.

=IF(ISDATE([Contract Expiration Date]@row), IF([Contract Expiration Date]@row < TODAY(0), "Red", IF([Contract Expiration Date]@row < TODAY(90), "Yellow", "Green")), "Gray")

birdie1087

This worked! Thank you so much!!!

Carson Penticuff

Awesome, happy to help!

birdie1087

Trying to take it further and not sure it's possible….

Still working with the dots - It is possible to have two if statements?

For instance, if it want it to look at the status column first and then the date column?

I would only want those with a status of "In Process" to then look at the date. All other statuses I'd want to be gray.

Not sure this makes sense?

birdie1087

I think I figured it out!