On Time and Past Due Formulas

mbreidenstein

Hello,

I am trying to create a formula in Smartsheet that replaces a formula we are using in Excel that turns a cell red or green if the date entered is before or after a referencing cell date. If that makes sense. 

So – If the Review SOW date cell in a column is in the past and the Review SOW Complete cell is blank, that cell and/or date will turn red and stay red even after a date is entered because it was past due.

Then, if the Review SOW date cell is either in the future and the Review SOW Complete is less than or equal to the Review SOW date, then that cell turns green after a date is entered.

We currently have this built into Excel but would like to move it to Smartsheet. We report out on metrics on how many milestones are on-time vs late but we need something that shows this visually in our tracker.

Any help or suggestions would be greatly appreciated because I'm stumped at the moment. Thanks!

heyjay

There is a similar functionality -

mbreidenstein

Thank you! I tried doing this but the issue I run into is, each row has different dates that are populated from a date in the future. So by setting the condition to a date, it would only apply to one cell accurately. I've also tried to use conditional formatting as well, but once a date is in the past, whether or not it was on-time, it flags it as red and "late".

This is what led me to a formula because in my mind, it would give me more flexibility to say, "if Review VKO is in the past and Review VKO Complete is blank and/or greater than the Review VKO, than apply..." or "if Review VKO is in the future and the Review VKO Complete is less than or equal to the Review VKO date, then apply.."

Maybe I'm just missing it...

Kelly Moore

Hey @mbridenstein

The conditional formula for the [Review SOW] field is straight forward. It is red if the date is in the past. A watch-out, however, is that all dates are eventually in the past as time marches forward. Is there some other field that marks the row as complete so you can turn the off the count before the everything turns red? If so, you can add an AND clause to your conditional format criteria.

Since the [Review SOW Complete] criteria is bit more complex, an alternative is to use a helper column that contains a formula for your [Review SOW Complete] field, as you suggested. You can then use conditional formatting keyed off that helper column to color code whatever fields you want colored.

You can make the helper column formula display whatever you want it to display as the trigger- I chose to make the formula below display a color word.

Helper column for [Review SOW Complete] conditional formatting:

=IF(AND(ISDATE([Review SOW]@row, [Review SOW]@row>TODAY(),[Review SOW Complete]@row=""), "Red", IF(OR([Review SOW Complete]@row>TODAY(),[Review SOW Complete]@row<=[Review SOW]@row, "Green")

Would this work for you

Kelly