Formula Help - using multiple IFBLANKS in a single formula

Trying to make this work, but keep hitting a wall. We've built an escalation portal, and now I'm trying to pull some metrics out of it, including duration as a P1.

I have 4 cells, P1 Date, P2 Date, P3 Date, P4 Date. I am trying to calculate the total number of work days between the P1 date and whatever the next date is, which is comlicated as not all rows go sequentially.. some go from P1 to P3 or straight to P4.

The formula below works from P1 to P3, but I cannot get it to work for a P1 to a P4.

=IF([Incident Status]@row = "P1 (Red)", NETWORKDAYS([P1 Date]@row, TODAY()), NETWORKDAYS([P1 Date]@row, IF(ISBLANK([P2 Date]@row), [P3 Date]@row, [P2 Date]@row)))

I thought it would be a simple as modifying the code to this -

=IF([Incident Status]@row = "P1 (Red)", NETWORKDAYS([P1 Date]@row, TODAY()), NETWORKDAYS([P1 Date]@row, IF(ISBLANK([P2 Date]@row), IF(ISBLANK([P3 Date]@row, [P4 Date]@row, [P3 Date]@row), [P2 Date]@row)))

... and I get an Invalid Data Type error.

Any thoughts?

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

Hi @MOC,

Would something like this work?

=IFERROR(IF([Incident Status]@row = "P1 (Red)", NETWORKDAYS([P1 Date]@row, TODAY()), NETWORKDAYS([P1 Date]@row, MIN([P2 Date]@row:[P4 Date]@row))), "")

If you're wanting to calculate the NETWORKDAYS between P1 and the next step (whether that is P2, P3 or P4), this should work. If will also function if you have multiple P values getting filled in.

Sample output:

If you had multiple P2-4 getting filled out and you wanted it P1 to the last date, then simply switch the MIN to MAX.

Hope this helps somewhat, let me know if I've misunderstood something or you've any problems/questions. 🙂

All comments

Nick Korna

Hi @MOC,

Would something like this work?

=IFERROR(IF([Incident Status]@row = "P1 (Red)", NETWORKDAYS([P1 Date]@row, TODAY()), NETWORKDAYS([P1 Date]@row, MIN([P2 Date]@row:[P4 Date]@row))), "")

If you're wanting to calculate the NETWORKDAYS between P1 and the next step (whether that is P2, P3 or P4), this should work. If will also function if you have multiple P values getting filled in.

Sample output:

If you had multiple P2-4 getting filled out and you wanted it P1 to the last date, then simply switch the MIN to MAX.

Hope this helps somewhat, let me know if I've misunderstood something or you've any problems/questions. 🙂

MOC

Awesome! Thank you Nick! The only note is the Px Date columns had to be sequential, which wasn't a big deal to change (I had a different calculator column between each date field but those were moved).

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.