Conditional Formatting: Comparing one Date column to Another
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.
Answers
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!