Health Balls & Formula (Today's Date v. Follow-Up Date)
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
Answers
-
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
-
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")))
-
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.
-
Red = 2+ weeks (14 day mark)
Yellow = after the 1 week mark
Green = only once the Contract Date is complete
-
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!
-
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"
-
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).
-
@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.
-
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)?
-
@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!
-
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! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!