Formula that ignores blank dates

Laura J

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

Kelly P.

@Laura J

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!

Kelly P.

@Laura J

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!