Health Balls & Formula (Today's Date v. Follow-Up Date)

Susan Swisher

I need a formula (for Red, Yellow, Green) Health Balls in the "Follow-Up Status" Column, based on the date in "Follow-up Date" Column

Red=2 weeks from Today

Yellow=1 week from Today

Green="Contract Executed Date" is NOT Blank

Kimberly Loveless

I think that this should work...

=IF([Follow-Up Date]@row>=TODAY(+14),"Red",IF([Follow-Up Date]@row>=TODAY(+7),"Yellow",IF([Contract Executed Date]@row<>"", "Green")))

This would put anything greater or equal to 2 weeks out as Red, anything between 1 and 2 weeks out as yellow, and if there is anything in the contract executed date column as Green

Susan Swisher

Interesting; so it is working if the Contract Executed Date is completed; but IF it is BLANK, it isn't

=IF([Follow-Up Date]@row >= TODAY(+14), "Red", IF([Follow-Up Date]@row >= TODAY(+7), "Yellow", IF([Contract Executed Date]@row <> "", "Green")))

Nick Korna

There is no criteria set for this in the formula - the date in the "Follow-Up Date" is less than 7 days (ruling out red/yellow) and the "Contracted Executed Date" is blank so green is also ruled out.

What result would you want to see in this case, and then the formula can be adjusted to reflect this.

Susan Swisher

@Nick Korna

Red = 2+ weeks (14 day mark)

Yellow = after the 1 week mark

Green = only once the Contract Date is complete

Nick Korna

I'm still not 100% clear on the desired outcome - is it like one of the status columns or something else (if so, what?) in the below example:

Sorry for being dense and not quite understanding what you're after!

Susan Swisher

@Nick Korna

OH my goodness... I am not thinking straight. My formula was working ALL along. I was actually putting the dates in "backwards". I kept putting in a "previous" date instead of a "forward" date to test my theory. "oy"

Nick Korna

That would certainly not help matters! 🤣

I wasn't sure if you would also want another status to highlight anything under a week but still in the future (adding a gray/blue option as desired, possibly reversing yellow/red/other colour so the "red" would be the most urgent).

Susan Swisher

@Nick Korna I am so over this FORMULA; and everything I do; it doesn't work. HELP!

=IF([Follow-Up Date]@row <= TODAY(-14), "Red", IF([Follow-Up Date]@row <= TODAY(-7), "Yellow", IF([Contract Executed Date]@row <> "", "Green")))

It is working "as expected" for the 2 weeks (BEFORE) today's date and return RED balls; however, NOW, it will NOT return GREEN balls when the Contract Executed Date is not blank.

Nick Korna

Shift the nesting of your IFs around slightly:

=IF([Contract Executed Date]@row <> "", "Green", IF([Follow-Up Date]@row <= TODAY(-14), "Red", IF([Follow-Up Date]@row <= TODAY(-7), "Yellow")))

As you can see, a follow-up date in the future will give you a blank result, having blank in both Dates will give you a red - would you want these outcomes changing (either by adding another colour or just making the double blank give no result at all)?

Susan Swisher

@Nick Korna Perhaps we add a "grey" or "blue" ball that would indicate we haven't "started" the follow-up yet; I would hate everything to be RED (to your point). What would that look like?

PS; SHIFTING the nested statement worked!

Nick Korna

Let's kill 2 birds with 1 stone - for rows with both dates blanks have them blank and add in a blue status for follow-up dates in the future (or not at the week mark):

=IF(AND([Contract Executed Date]@row = "", [Follow-Up Date]@row = ""), "", IF([Follow-Up Date]@row >= TODAY(-6), "Blue", IF([Contract Executed Date]@row <> "", "Green", IF([Follow-Up Date]@row <= TODAY(-14), "Red", IF([Follow-Up Date]@row <= TODAY(-7), "Yellow")))))

Using the similar info to above:

I've made a slight modification to include the stuff from today up to 6 days old in blue as otherwise these would come out as blanks due to not fitting into any of the other categories. If you want them to appear like this (as in the "Alternative" column above), then the formula is:

=IF(AND([Contract Executed Date]@row = "", [Follow-Up Date]@row = ""), "", IF([Follow-Up Date]@row >= TODAY(), "Blue", IF([Contract Executed Date]@row <> "", "Green", IF([Follow-Up Date]@row <= TODAY(-14), "Red", IF([Follow-Up Date]@row <= TODAY(-7), "Yellow")))))

This could also be modified if you wanted values older than today but less than 2 weeks old to be included in yellow (as demonstrated with "Alternative 2"):

=IF(AND([Contract Executed Date]@row = "", [Follow-Up Date]@row = ""), "", IF([Follow-Up Date]@row >= TODAY(), "Blue", IF([Contract Executed Date]@row <> "", "Green", IF([Follow-Up Date]@row <= TODAY(-14), "Red", IF([Follow-Up Date]@row >= TODAY(-13), "Yellow")))))

If you wanted to use a grey ball instead in any of the above formulas, then you would just change the symbol type in the column and "Blue" to "Gray" in the relevant formula.

Pick whichever option(s) you prefer, and hopefully this irons out any issues for you! 🙂