Formula to flag a task if not completed before due date

DanielleElise

I'm really struggling to get the syntax right on this.  I have a column called "% Complete" and a due date column called "Finish."  If the % Complete < 100 and the due date is today or already passed, I want to turn on a flag in the "At Risk" column.  Ideally I'd actually like to flag it if the due date is tomorrow or already passed, so we have more of a heads up in our reports, but I don't know if there's a way to do that at all. I'm assuming there needs to be a nested =IF(AND(..., but I don't know how to format the rest, what the column numbers would be, etc.  I also don't know how to put it so that it applies to the entire column (or every task in the column).  The only time I wrote a formula that wasn't unparseable (though it didn't work properly), it only applied to the first cell in the column.

Thank you!

 

 

Brian W

Here's the formula you're looking for:

=IF(AND([Finish]@row < TODAY(2), [% Complete]@row < 1), 1, 0)

You'll need to paste it into the cells of the At Risk column. You can't apply it to the entire column. Make sure your At Risk column type is set to flag. The 2 in TODAY(2) refers to 2 days after today, so < TODAY(2) is tomorrow or before.

Let me know if you need help making it work.

DanielleElise

Ah, thank you so much!  How do I get it to apply to the entire column, though?  I clicked the column header to select all, then clicked the function button and entered it in, but it looks like it only applied to the first row.

Brian W

Unfortunately, in Smartsheet you can't paste to an entire column. You have to select all the cells in the column you want to paste to and paste that way. You can also select the first cell with the formula and drag/copy it down.

Yulichka

@Brian W you can convert the Row Formula to a Column Formula, saving you the time of dragging/copying the formula down.