IF AND OR not working properly, last IF not triggering anything

crystal.gonzalez.ND

Please help me figure out why this is not working:

=IF([% Complete]@row = 1, "Completed", IF(AND([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Not Started", IF(AND(NOT(ISBLANK([Start Date]@row )), NOT(ISBLANK([% Complete]@row ))), "In Progress", IF(AND([Start Date]@row >= TODAY(61), [% Complete]@row = 0), "Upcoming", IF(ISBLANK([Start Date]@row ), "Upcoming")))))

The last piece of the formula is not triggering a change to "Upcoming". If a date is greater than 61 days it does change the status to "Upcoming", but if Start Date is blank instead of changing to "Upcoming" it changes to "Not Started" and I am not sure why. Please help!

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @crystal.gonzalez.ND

Simple fix:
The reason your last IF(ISBLANK([Start Date]@row ), "Upcoming") isn’t firing is because Smartsheet treats a blank date as 0, so the earlier condition
[Start Date]@row <= TODAY(60)
is already TRUE when Start Date is blank.
Just move the blank-date check higher in the formula so it’s evaluated first:

=IF([% Complete]@row = 1, "Completed",

IF(ISBLANK([Start Date]@row), "Upcoming",

IF(AND([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Not Started",

IF(AND(NOT(ISBLANK([Start Date]@row)), NOT(ISBLANK([% Complete]@row))), "In Progress",

IF(AND([Start Date]@row >= TODAY(61), [% Complete]@row = 0), "Upcoming")))))

This way, blank dates will be correctly categorized as “Upcoming.”

https://app.smartsheet.com/b/publish?EQBCT=b6be80e235884fe89c3a9fcce61ac9c6

Shorter version (clean rewrite):
Your nested ANDs overlap — earlier IFs already cover those conditions, so the later ones never trigger.
Here’s a simplified, readable version with the same logic:

=IF([% Complete]@row = 1, "Completed",

IF(ISBLANK([Start Date]@row), "Upcoming",

IF([% Complete]@row > 0, "In Progress",

IF([Start Date]@row <= TODAY(60), "Not Started", "Upcoming"))))

Blank Start Date → “Upcoming” handled early.
0% complete but within 60 days → “Not Started.”
0% → “In Progress.”
Everything else (future start) → “Upcoming.”

Both work, but the second is much easier to maintain.

crystal.gonzalez.ND

This seemed to work! I do have one more question, if you look at the below bullet points, I slightly changed my parameters that are bolded below, added BLANK to the second bullet and changed the 3rd to 1% or more. How would the formula above change? And I do apologize I do not use formulas as often as I should and am a bit rusty

Blank Start Date → “Upcoming” handled early.
0% complete OR Blank, but within 60 days → “Not Started.”
1% or more → “In Progress.”
Everything else (future start) → “Upcoming.”

jmyzk_cloudsmart_jp

As the image below shows, both 0% complete and blank % complete are treated as zero. So, the following condition catches both 0% complete and blank % complete cases as false cases.

IF([% Complete]@row > 0, 
  [true: example 0.1%, 0.49%, 0.5%, 1%, 25%, 100% ..], 
  [false: example 0%, blank])

Regarding the 1% or more, I would advise sticking with the >0 condition if you want to categorize 0.5% complete as In Progress.

0.5% is displayed as 1% when you decrease the decimal, whereas 0.49 is shown as 0%.

So, it's better to use a formula that works regardless of decimal settings.

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.