Conditional Formatting Comparing Dates
I want to have conditional formatting comparing 2 dates:
Where [Actual] > [Due Date], column will be red
Where [Actual] < = [Due Date], column will be green
Where [Due Date] has a value but [Actual] is blank, I would like to assume today's date for Actual.
Where [Due Date] is blank, but [Actual] is not, I'd like it to be green.
Where [Due Date] & [Actual] are blank, I would like it to not evaluate/not be colored red or green.
I know I need a helper column, but I'm getting stuck on how to implement it to evaluate properly where dates are left blank.
Best Answer
-
Give this a try:
=IF([Due Date]@row <> "", IF([Due Date]@row > IF(Actual@row <> "", Actual@row, TODAY()), "Green", "Red"), IF(Actual@row <> "", "Green"))
Answers
-
The first part is easy =IF([Actual]@row > [Due Date]@row, "Red", IF([Actual]@row < = [Due Date]@row, "Green"
As for the second part….
=IF(AND([Due Date]@row = "", Actual@row = ""), " ", IF(AND([Due Date]@row <> "", Actual@row = ""), IF(TODAY() > [Due Date]@row, "Red", IF(TODAY() < =[Due Date]@row, "Green")), IF(AND([Due Date]@row = "", Actual@row <> ""), "Green")))
-
Give this a try:
=IF([Due Date]@row <> "", IF([Due Date]@row > IF(Actual@row <> "", Actual@row, TODAY()), "Green", "Red"), IF(Actual@row <> "", "Green"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!