Nested IF(AND) formula not doing what I think it should

This formula is returning Past Due when the Due Date is blank and there is a date in the Start Date field. This is not what I want. Instead, when I have a date in the Start Date field, I want it to say "Not Started" or In Progress based on the date entered. What am I doing wrong?

=IF(AND([Start Date]@row = "", [% Complete]@row = ""), "Need Start Date", IF(AND([Due Date]@row = "", [% Complete]@row = ""), "", IF(AND([Due Date]@row < TODAY(), [% Complete]@row < "1"), "Past Due", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = ""), "Not Started", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row < "1"), "In Progress", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = "1"), "Complete", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row = "1"), "Complete")))))))

Thank you!

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @steven.nienaber105336

Two things here:

Firstly, if your % Complete column has numeric data in it, you will want to remove the quotes around the "1" as this turns it into a text string. You can say [% Complete]@row < 1

Secondly, a blank date cell is seen as "in the past". This means that if you have a Blank Due Date and a % Complete that is Less Than 1, it's seen as "Past Due". You'll just need to add in that the Due Date cannot be blank (<> "") as part of your criteria for "Past Due".

Try this:

=IF(AND([Start Date]@row = "", [% Complete]@row = ""), "Need Start Date", IF(AND([Due Date]@row = "", [% Complete]@row = ""), "", IF(AND([Due Date]@row < TODAY(), [Due Date]@row <> "", [% Complete]@row < 1), "Past Due", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = ""), "Not Started", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row < 1), "In Progress", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = 1), "Complete", IF(AND([Start Date]@row <= TODAY(), [% Complete]@row = 1), "Complete")))))))

Let me know if this works for you!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @steven.nienaber105336

Two things here:

Firstly, if your % Complete column has numeric data in it, you will want to remove the quotes around the "1" as this turns it into a text string. You can say [% Complete]@row < 1

Try this:

Let me know if this works for you!

Cheers,

Genevieve

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.