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
Comments
-
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
-
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"))
-
Thanks for the assist on that one Paul.
-
-
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
-
David,
Happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!