Formula that ignores blank dates
I have 4 date fields, Target Start, Actual Start, Target End, and Actual End. I need to turn the cell yellow if the date is out of sync but handle that some dates can be blank or equal.
• I may have a Target Start date, but not have an Actual Start date, Target End date, or Actual End date yet  which is OK and should not highlight any cells yellow.
• It is OK if Target Start date and Actual Start date are the same. It is OK if Target End date and Actual End date are the same.
Target Start cannot be greater than Target End
Actual Start cannot be greater than Target End
Actual Start cannot be greater than Actual End
Actual Start cannot be greater than today
Actual End cannot be greater than today
I assume this will take some helper columns for the formulas and conditional formatting, just not sure the best way to proceed. Any help would be appreciated. Thanks
Best Answer

A row could potentially have multiple errors (e.g., a target start that is greater than the target end and an actual start that is greater than the target end and an actual start that is greater than today). To identify and conditionally format each potential combination of errors, you will want to use the helper columns. Each helper column could have a formula structured similarly to the one below (built to disregard blank dates). The formula below populates an error message (which might be helpful for including in an update request), but you could also just use checkboxes.
=IF(AND(ISDATE([Target Start]@row), ISDATE([Target End]@row)), IF([Target Start]@row > [Target End]@row, "Target Start > Target End"))
Hope this helps!
Answers

A row could potentially have multiple errors (e.g., a target start that is greater than the target end and an actual start that is greater than the target end and an actual start that is greater than today). To identify and conditionally format each potential combination of errors, you will want to use the helper columns. Each helper column could have a formula structured similarly to the one below (built to disregard blank dates). The formula below populates an error message (which might be helpful for including in an update request), but you could also just use checkboxes.
=IF(AND(ISDATE([Target Start]@row), ISDATE([Target End]@row)), IF([Target Start]@row > [Target End]@row, "Target Start > Target End"))
Hope this helps!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!