Formula to flag a task if not completed before due date

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!

Find more posts tagged with

Sheets

Formulas

Legal

IT & Operations

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Help with High Priority Function
Hi! I am relatively new to Smartsheet and trying to figure out how to program a sheet I am working on. I have one sheet holding many rows, each row representing a different project. For each project, the user is able to mark the priority as high, medium, or low, using the built in Smartsheet symbols. The goal is to make it…
Counting specific names in a table from a specific drop down menu
Hello everyone, I am trying to create a cell that calculates the number of times a certain name shows up whenever a criteria is chosen from a dropdown menu. Currently, I have Column E with a dropdown menu of names (uses data from Column L). Column F will use that cell as a reference to populate a cell using VLOOKUP…
Conditional Formatting Query
All the above rules are working except the 4th one in the list which asks for the cell to turn red if Group status is not started and end date is in next 14 days. I have used the Smartsheet AI tool but no luck, I have saved and refreshed. I am using a helper column for my 5th rule in the list which asks the cell to turn…