IF(AND(ISBLANK Formula Help

I am utilizing the following formula to return a value based on the values in my columns:

=IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row > TODAY()), "Scheduled", IF(AND([Work Needed]@row = true, [Scheduled Inspection Date]@row < TODAY()), "In Progress", IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row < TODAY(7)), "Completed", IF(AND([Work Needed]@row = false, [Scheduled Inspection Date]@row < TODAY(7)), "Completed"))))

I would like to add "Not Scheduled" if the Work Needed Column is false and the Scheduled Inspection Date column is blank. I have been at it for hours with no solution. Any help is greatly appreciated in advance!

Find more posts tagged with

Accepted answers

All comments

Kelly Drake

So first... it looks like the last two IF statements in your formula are identical.... is that an error or are you missing one set of conditions for 'Completed'? Also "= true" is not the right syntax for smartsheet, you would need to use = 1. And it looks like you would never end up with a line getting flagged as 'Completed'...

Also, I typically work from Completed backwards to not started to make sure things land in the spot on the process. But it looks like the only one that has Work Neded checked is for 'In Progress' so you could pull this one to the front and simply your formula a bit

What about the following....

= IF([Work Needed]@row = 1, "In Progress", IF(ISBLANK([Scheduled Inspection Date]@row), "Not Scheduled", IF([Scheduled Inspection Date]@row < TODAY(7), "Completed", IF([Scheduled Inspection Date]@row > TODAY(), "Scheduled"))))

Michelle Fayed

@Kelly Drake Worked PERFECTLY! Thank you so very much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.