Conditional Formatting: Comparing one Date column to Another

Jill Sullivan

I am trying to setup a rule to format a date Red if it is different from another column.

For Example, I have two end date columns, the New finish date is the active date but I want to see where this is different from the Old Finish date we had previously agreed upon.

Old Finish Date

New Finish Date

I want new finish date to be red if its later then old finish date AND I also want it to be red if the date is in the past and not 100% complete.

Staylo95

Hi Jill,

You cannot directly make conditional formatting reference another cell but you can add helper columns and drive the conditional formula based on those.

Based on your description, I wrote the following formulae for two columns. Someone might be able to write it into 1 column but I got stuck there.

Helper column formula:

=IF([New Date]@row > [Old Date]@row, 1, 0)

Overdue formula:

=IF(AND([New Date]@row >= TODAY(), [% Complete]@row < 1), 1, 0)

You could then base your conditional formatting based on the flags and you could hide these columns if you didn't want to see them (I would advise converting them to column formulae)

1 caveat is that the TODAY function will only work if someone views the sheet. Therefore, if you want to set up notifications based on a flag using TODAY, they will not send if no one looks at the sheet.

To get around this, all of my TODAY formulae reference a single sheet that is opened daily.

Further help:

Today function: https://help.smartsheet.com/function/today

Available Symbols: https://help.smartsheet.com/articles/2480316-available-symbols-in-symbols-column

Hope this helps!