using IF together with AND

Sorry in advance if this has been answered but I have searched and cannot see the answer but I am sure its simple!

I have target finish and actual finish columns, in the status column, I want to report "in progress", "overdue" and "Complete"

I have got "in progress", "overdue" to work by the following:

=IF(TODAY() < [Target finish]3, "In Progress", "Overdue")

What I am struggling with is how to have return "Complete" if the "Actual Finish" column has a date value.

Thanks in advance.

David

Find more posts tagged with

Construction

Formulas

Gantt View

Project Management

Comments

Michael Kingsbury

I would use ISBLANK() Funtion nested in the if statement. For example:

=ISBLANK(Actual Finish@row, IF(TODAY() < [Target finish]@row, "In Progress", "Overdue"),"Complete")

Logic: If Actual Finish is blank then the IF statement will determine if its overdue or not. If Actual Finish is NOT blank then it will return Completed.

Try that out and let me know.

Michael

Paul Newcome

You missed the leading IF and didn't close out the ISBLANK statement. You also need square brackets around the Actual Finish column name.

=IF(ISBLANK([Actual Finish@row]), IF(TODAY() < [Target finish]@row, "In Progress", "Overdue"),"Complete")

A second way of writing it would be

=IF(ISDATE([Actual Finish]@row, "Complete", IF(TODAY() < [Target Finish]@row, "In Progress", "Overdue"))

Michael Kingsbury

Thanks for the assist on that one Paul.

Paul Newcome

No worries.

David Muskett

Dear Michael and Paul,

Thanks a million for your help, works (both) perfectly.

I have added a little enhancement to tidy the sheet so that if "Issue" has no data then your formula does not return anything.

=IF(ISBLANK(Issue1), "", IF(ISBLANK([Actual finish]1), IF(TODAY() < [Target finish]1, "In Progress", "Overdue"), "Complete"))

That way, if no issue is logged, then the "complete/in progress/overdue" cell will be empty.

Plus conditional formatting to have "complete/in progress/overdue" coloured green,yellow,red.

Love the community spirit of Smartsheet users.

Thanks again,

David

Paul Newcome

David,

Happy to help.

Sign in to join the conversation!

Quick Links

Trending discussions

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Need help in formula function
I'm not able to get this formula to function in Smartsheet. The material ordered date is 9/3/2025. Am I missing something to get the date rage to populate? =TEXT([Material Ordered Date]@row +28,"MM/DD/YYYY")&"-"&TEXT([Material Ordered Date]@row +42, "MM/DD/YYYY")
I am trying to find a value in a table based on 2 criteria.
I am trying to find a value in a table based on 2 criteria. One of the values is dynamic from a drop down menu, while the other is static value in a column. The first value is found in column "Building type" and the other is found in Resource type. The table I am referencing is also shown. The result would be shown in the…