Turn cell Green, Yellow or red depending on how many days have elapsed past a date in another column

HarveyL

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.

Kelly Drake

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

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.

HarveyL

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")), "")

1. Still can't nail the 8-16 days to turn yellow.
2. 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?"

Kelly Drake

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

HarveyL

Thanks Kelly. I was asked to make some adjustments to this process and created a new post for simplicity. Appreciate your help.