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?
Best Answer
-
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. 🙂
Answers
-
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. 🙂
-
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).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!