Nested IF formulas for status

I am trying to write a nested IF formula, but as I run it there is one argument that is not resulting as I expected.

Basically what I want my Status column to represent is the following

-Done: when a project is in 100% compliance

-In Progress: when a project is anywhere from 1%-75% and its within the due date (the compliance % column is a Single select-Drowpdown list with options 1%, 25%, 50%, 75%, 100%)

-Not started: when a project is in 1% compliance but within the due date

-Delayed: when a project is not finished and the due date has expired OR when a project hasn't been started and today (as in any given day the report is consulted) is in less than 10 days from the due date.

I added the ¨Expected Result¨ column for a better appreciation of where I'm trying to get at

This is the nested formula that I am using:

=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row = 0.01, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))

I hope I was sufficiently clear and that you can help me,

Thanks!!

Find more posts tagged with

Formulas

Accepted answers

All comments

Heather Duff

Hi @Zulma PH ,

I think I see the problem. Your "delayed" portion of the formula is currently checking for equal to 0.01.

=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row = 0.01, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))

The row you have indicated that should be displaying Delayed is showing Not Started because it is not equal to 1%. Perhaps you want to change that portion of the formula to reflect >=0.01

Let me know if that works for you!

Best,

Heather

Zulma PH

https://community.smartsheet.com/discussion/comment/318953#Comment_318953

Thank you, but that part of the formula is actually what I need to establish to comply with the second part of this statement:

--Delayed: when a project is not finished and the due date has expired OR when a project hasn't been started and today (as in any given day the report is consulted) is in less than 10 days from the due date.

Heather Duff

@Zulma PH , could you possibly use this, then?

=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due Date]@row > TODAY()), "In Progress", IF((AND([Compliance (%)]@row < 0.01, [Due Date]@row < TODAY())), "Not Started", IF(AND([Compliance (%)]@row <1, TODAY() > [Due Date]@row - 10), "Delayed", "Not Started"))))

That way it's indicating that it is less than 100% done, but doesn't necessarily have to be equal to 1%.

Zulma PH

https://community.smartsheet.com/discussion/comment/319017#Comment_319017

Thank you for your support Heather, that didn't quite do the trick, because the percentage did have to be 1% for that rule to apply, how ever it made me wonder if the priority of my rules was the problem and I could solve it with this formula using an OR function for the Delayed part :

=IF([Compliance (%)]@row = 1, "Done", IF(AND([Compliance (%)]@row >= 0.25, [Compliance (%)]@row <= 0.75, [Due date]@row > TODAY()), "In Progress", IF(OR(AND([Compliance (%)]@row = 0.01, TODAY() >= [Due date]@row - 10), AND([Compliance (%)]@row < 1, [Due date]@row < TODAY())), "Delayed", "Not Started")))

Heather Duff

@Zulma PH Great thinking! Did that work for you?

Zulma PH

https://community.smartsheet.com/discussion/comment/319022#Comment_319022

Yes! I tried it with all my projects now and haven't had any issues

Thank you!

Heather Duff

Fantastic! Glad you found something that worked.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.