If And formula help

I'm trying to create a formula that will give the project status based on start date, end date, and complete flag. I have been working on the following formula for a bit, trying different iterations, but it just wont work. If the record does not have a Start date, then Status should be TBD, else, if Start date is greater than or equal to Today and Due date is greater than 15 days from today, then status is On Track, else if Start date is greater than or equal to Today and Due date is less than 15 days from today then status is Coming Due.

=IF([Complete]@row = 1, "COMPLETE", IF([Start Date]@row = "", "TBD", IF(and([Start Date]@Row > Today(),[Due Date]@row <= (TODAY(+15))), "COMING DUE", IF([Start Date]@Row >= Today(), "ON TRACK")), "NOT STARTED"))

Find more posts tagged with

Formulas

Accepted answers

Mark.poole

@Juan Pimentel the reason for this is it is finding all of the previous statements as false.
I think I see the problem.

=IF(Complete@row = 1, "COMPLETE", IF(ISBLANK([Start Date]@row), "TBD", IF(AND([Start Date]@row > TODAY(), [Due Date]@row <= TODAY(15)), "COMING DUE", IF([Start Date]@row <= TODAY(), "ON TRACK", "NOT STARTED"))))

see if the last little adjustment I did fixed the issue for you

All comments

Adam Murphy

Try this: =IF([Complete]@row = 1, "COMPLETE", IF(ISBLANK([Start Date]@row), "TBD", IF(AND([Start Date]@row >= TODAY(), [Due Date]@row > TODAY() + 15), "ON TRACK", IF(AND([Start Date]@row >= TODAY(), [Due Date]@row < TODAY() + 15), "COMING DUE", "NOT STARTED")))

Mark.poole

@Juan Pimentel

It looks like you were really close try this.

=IF(Complete@row = 1, "COMPLETE", IF(ISBLANK([Start Date]@row), "TBD", IF(AND([Start Date]@row > TODAY(), [Due Date]@row <= TODAY(15)), "COMING DUE", IF([Start Date]@row >= TODAY(), "ON TRACK", "NOT STARTED"))))

Juan Pimentel

@Adam Murphy @Mark.poole thanks for the responses! Both formulas gave me "NOT STARTED" in a record where Start Date is 3/1/25 and End Date is 12/15/25.

Mark.poole

@Juan Pimentel the reason for this is it is finding all of the previous statements as false.
I think I see the problem.

see if the last little adjustment I did fixed the issue for you

Adam Murphy

Mark's adjustment should work, I agree.

Juan Pimentel

@Adam Murphy @Mark.poole

It worked! thank you both for the assistance!!!!

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.