Date formula - PastDue/Ontime/Ongoing or remain blank ?

Currently using a formula (which works) which states if a column is past due if posted after the target date. Hoping to get help with updating it so that if the date isn't filled in yet it will say "Ongoing" or remain blank.

=IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time")

Thank you very much!

Find more posts tagged with

Accepted answers

Paul McGuinness

Hi @Morris R

This formula should do what you need, I assumed it was the [Actual End Date] being blank that would trigger the ongoing response. If not you can just rename to [Targeted End Date].

=IF(ISBLANK([Actual End Date]@row),"Ongoing",IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time"))

Hope that helps

Thanks

Paul

All comments

Paul McGuinness

Hi @Morris R

This formula should do what you need, I assumed it was the [Actual End Date] being blank that would trigger the ongoing response. If not you can just rename to [Targeted End Date].

=IF(ISBLANK([Actual End Date]@row),"Ongoing",IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time"))

Hope that helps

Thanks

Paul

Morris R

Perfect that worked, Thank you, I see how you added it in there!

I appreciate the quick and easy response!

Morris R

Hello - Back again. Using your formula has worked great! no issues. Hoping to add another wrinkle?

Blank: shows "ONGOING" (works)

Targeted end date < Actual end date = "Past Due" if not its "On time" (works)

CURRENT USING: =IF(ISBLANK([Actual End Date]@row), "Ongoing", IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time"))

Add in: Completed "EARLY"

So essentially if a project ends earlier than project "Target Date" then its "EARLY" , if it ends on the same day its "ONTIME" , if its past its "PAST DUE" - In addition to "Actual End date" is blank its "ONGOING"

Thank you again for your help!

Genevieve P.

Hi @Morris R

Try adding this statement:

=IF(ISBLANK([Actual End Date]@row), "Ongoing", IF([Targeted End Date]@row > [Actual End Date]@row, "Early", IF(AND([Targeted End Date]@row < [Actual End Date]@row, Status@row <> "Closed"), "Past Due", "On time")))

Cheers,

Genevieve

Morris R

Fantastic thank you that worked!

I was stuck on order placement.

Thank you again!

Genevieve P.

Glad I could help! 🙂

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.