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

Options

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

Best Answer

  • Gavin Seaton
    Gavin Seaton ✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    Options

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

    ...

  • Gavin Seaton
    Gavin Seaton ✭✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!