Removing Condition if the Value is blank

I have a formula that shows the Status of a milestone and it currently reads the due date (Finish) and automatically updates to off track if the due date has passed and the status if not in at risk or completed. However, I cant seem to get the formula to the point where it will not mark the milestone as off track if there is no date in the Finish column. Currently my formula looks like this.

=IF(AND(Finish1 < TODAY(), Status1 <> "At Risk", Status1 <> "Completed"), "Off Track")

Any ideas on how I can add a cell ISBLANK don't mark as off track condition to this?

Find more posts tagged with

Formulas

functionality

Accepted answers

David Tutwiler

=IF(ISBLANK(Finish1), "No Date", IF(AND(Finish1 < TODAY(), Status1 <> "At Risk", Status1 <> "Completed"), "Off Track"))

That should do the trick.

David Tutwiler

Just a paren placement issue I think and also an OR instead of an AND. Try:

=IF(OR(ISBLANK(Finish10), Status10 = "N/A"), "No Date", IF(AND(Finish10 < TODAY(), Status10 <> "Complete"), "Past Due"))

All comments

David Tutwiler

=IF(ISBLANK(Finish1), "No Date", IF(AND(Finish1 < TODAY(), Status1 <> "At Risk", Status1 <> "Completed"), "Off Track"))

That should do the trick.

Ryan Jared

Yes it did, Thank you!

David Tutwiler

No problem. Glad it's working.

Ryan Jared

I hit a roadblock that I didn't anticipate and was hoping you could help again.

I didn't take into account rows were the Status is "N/A" and the Finish date is Blank. I tried to solve it using the below but it's coming back #UNPARSEABLE. Any idea on what I'm doing wrong? The bold is what i have added.

=IF(AND(ISBLANK(Finish10), Status10 <> "N/A", "No Date"), IF(AND(Finish10 < TODAY(), Status10 <> "Complete"), "Past Due")))

David Tutwiler

Just a paren placement issue I think and also an OR instead of an AND. Try:

=IF(OR(ISBLANK(Finish10), Status10 = "N/A"), "No Date", IF(AND(Finish10 < TODAY(), Status10 <> "Complete"), "Past Due"))

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.