Help with writing a formula to show Completed and Past Due along with other status.

Hi there, I am able to get all the status to show correctly, except the 'Complete' and 'Past Due', I know its something simple and I am just not thinking of it, help is appreciated!

I am using a 'End Date' and '% Complete'

Formula used on the 3rd date down

=IF(OR([% Complete]@row = 0, ISBLANK([% Complete]@row)), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 100), "Past Due", IF([% Complete]@row = 100, "Complete")))

Formula used on the 4th date down

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

Find more posts tagged with

Formulas

status

Accepted answers

Nic Larsen

I see another typo. I didn't close the AND agreement. Sorry about that:

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

All comments

Nic Larsen

Try:

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

Elizabeth Taft

Thank you so much for answering! I tried it and getting the below response now

Nic Larsen

Ooops, I left something off when I edited:

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

Elizabeth Taft

Is it copying and pasting correctly? I am getting the same response.

Nic Larsen

I see another typo. I didn't close the AND agreement. Sorry about that:

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

Elizabeth Taft

PERFECT!! it works, thank you!!

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.