Compare column dates only if task is finished

Options

Hello! I'm trying to set up a formula that will allow me to assign a value if my "Date Closed" column is later than "Target Close Date" column ONLY IF the task is complete.

So far, I've been able to get my base formula to work:

=IF([Date Closed]13 > [Target Close Date]13, "Yes", "No")

If the date closed is further out than target closed date, the cell in my "Delayed?" column is marked as "Yes". Otherwise, it's marked as "No". My issue is that even if the "Date Closed" is blank, the cell will be marked as "No".

How can I have the formula assign a blank value if the task is not complete? I have a "% Complete" column as well as a dropdown "Status" column

Best Answer

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓
    Options

    @Min

    Sorry I was distracted and got you column names mixed up

    =IF(ISDATE([Date Closed]@row), IF([Date Closed]@row > [Target Closed Date]@row, "Yes", "No"), "")

    If that doesn't work please post a snip of your formula

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!