Highlight date cell in a colour if blank and date has passed in another date cell

Gavin Seaton

Hi

We have several date columns that need to be compared and highlighted if dates have passed and corresponding column row does not contain a date.

Image shows 'Site Completion Forecast' with a date that is in the past. The 'Site Completion Actual' Column is blank for this row and needs to be highlighted (Red if possible).

I tried this formula but get an #unparseable

=AND(NOT(ISBLANK([Site Completion Forecast]@row), ISBLANK[Site Completion Actual]@row, [Site Completion Forecast]@row<=TODAY())

I also tried using an Automation but it doesn't let you update the value of a Date Column.

Gavin

Gavin Seaton

Thanks @heyjay but as far as i can see Conditional Formatting does not allow you to check a Cell value, compare it to another Cell value then apply formatting, but i could be missing something obvious.

I need it to check if:

1: Date in 'Site Completion Forecast' cell is in the Past

&

2: If 'Site Completion Actual' cell is Blank highlight this cell in Red

Hope that makes sense and any help would be appreciated.

Gavin

heyjay

Use the Conditional Formatting to highlight the cells in RED if its blank, empty, or "".

Gavin Seaton

Thanks @heyjay but as far as i can see Conditional Formatting does not allow you to check a Cell value, compare it to another Cell value then apply formatting, but i could be missing something obvious.

I need it to check if:

1: Date in 'Site Completion Forecast' cell is in the Past

&

2: If 'Site Completion Actual' cell is Blank highlight this cell in Red

Hope that makes sense and any help would be appreciated.

Gavin