Turn cell Green, Yellow or red depending on how many days have elapsed past a date in another column
Hello Smartsheet friends,
I've found several past due or days before past due formulas but have not been able to find one specific to this case.
We use a smartsheet for account management. I would like to have a cell turn a color based on the variables below, based on how many days have elapsed past the date in another row. There is no due date on this sheet. I simply need to draw attention to a column that needs to have a scheduled date within 21 days of the date in another column.
I greatly appreciate any help you can provide. Thanks in advance friends.
Column A - 1/1/2021
Column B
Green - 1-7 Days past the above date
Yellow - 8-16 days past the above date
Red - 17+ Days past the above date
When a date has been entered in another column, it would stop the counting or turn it blue.
Best Answer
-
So for date ranges, I actually work backwards so that by saying anything greater or equal to your 17 days is put into the red bucket then you go to the next farthest out and by saying greater or equal to 8 you'll end up with everything 8-16 in yellow and so on....
If it helps - when i do nested IF statements, I think about my data set as a pie and figure out how to "eat" each slice of pie in the most efficient formula as I can.
For the second part of your question - if you're just looking for any date to be in there (aka doesn't matter if its in the future or the past) you could use the following
IF(AND(NOT(ISBLANK([Activation Review Call Date(7-21 Days)]@row)), [Email Check-in Status]@row = 1), "Blue"
...and if tha'ts the end goal I would put that chunk of the formula right after your IFERROR(
Also - looks like you could optimize your data entry by leveraging a formula in 30 Day follow up column
= [Activation Review Call Date(7-21 Days)]@row +30
You could make this more optimized for business if you layer in a workday formula, but I'd have to look into how that works as I haven't leveraged those formulas https://help.smartsheet.com/function/workday
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
Answers
-
To turn the cell colors, you would need a helper column to trigger conditional formatting.
This formula could work using a RYGB ball column type as your helper
=IF(NOT(ISBLANK([Column B]@row)), "Blue", IF(TODAY() >= [Column A]@row +17, "Red", IF(TODAY() >= [Column A]@row +8, "Yellow", "Green")))
Then you could create conditional formatting rules to make the formatting match your helper column.
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
Hi @Kelly Drake ,
Thanks so much for the reply! I discovered the status option on smartsheets. I used the following formula.
=IFERROR(IF(TODAY() - [IC / PC Handover Date - From Live Deals_Formula]@row > 21, "Red", IF(TODAY() - [IC / PC Handover Date - From Live Deals_Formula]@row < 20, "Green", "Yellow")), "")
- Still can't nail the 8-16 days to turn yellow.
- I realize this will keep counting. Is there a way to create a formula "if activation review call date AND if email check-in status IS checked, turn activation call status Blue?"
-
So for date ranges, I actually work backwards so that by saying anything greater or equal to your 17 days is put into the red bucket then you go to the next farthest out and by saying greater or equal to 8 you'll end up with everything 8-16 in yellow and so on....
If it helps - when i do nested IF statements, I think about my data set as a pie and figure out how to "eat" each slice of pie in the most efficient formula as I can.
For the second part of your question - if you're just looking for any date to be in there (aka doesn't matter if its in the future or the past) you could use the following
IF(AND(NOT(ISBLANK([Activation Review Call Date(7-21 Days)]@row)), [Email Check-in Status]@row = 1), "Blue"
...and if tha'ts the end goal I would put that chunk of the formula right after your IFERROR(
Also - looks like you could optimize your data entry by leveraging a formula in 30 Day follow up column
= [Activation Review Call Date(7-21 Days)]@row +30
You could make this more optimized for business if you layer in a workday formula, but I'd have to look into how that works as I haven't leveraged those formulas https://help.smartsheet.com/function/workday
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
Thanks Kelly. I was asked to make some adjustments to this process and created a new post for simplicity. Appreciate your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!