IFERROR, NETWORKDAYS, and MATCH all in one?
Is it possible to have a formula that includes IFERROR, NETWORKDAYS, and MATCH?
I'm working with two different sheets, both of which have a start date and an end date column, along with a project ID column. In a third sheet, I need to reference the start and end dates of both sheets using the NETWORKDAYS formula to calculate the days that lapsed between start and completion, and I need it to pull based on matching project IDs.
My rudimentary formula looks like this right now:
=IFERROR(MATCH([PROJID]@row, {Sheet 1 PROJID}, NETWORKDAYS({Sheet 1 Start Date}, {Sheet 1 End Date}, 0)), "") + IFERROR(MATCH([PROJID}@row, {Sheet 2 PROJID}, NETWORKDAYS({Sheet 2 Start Date}, Sheet 2 End Date}, 0)), "")
I have probably flubbed this up something fierce, but any help would be appreciate, please!
Best Answer
-
There's the issue. Your formula is outputting as a text string. If you "add" "" anywhere, it automatically turns into a text string, so even though it is pulling a date in, it is converted to a text string on the back end by the outputs of the IFERRORs. You should nest each INDEX/MATCH into the second portion of the IFERROR to output data that is stored as date type data.
=IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Assigned Date}, MATCH(PROJID@row, {Sheet1 PROJID}, 0)), INDEX({Sheet2 Assigned Date}, MATCH(PROJID@row, {Sheet2 PROJID}, 0))), INDEX({Sheet3 Assigned Date}, MATCH(PROJID@row, {Sheet3 PROJID}, 0))), INDEX({Sheet4 Assigned Date}, MATCH(PROJID@row, {Sheet4 PROJID}, 0))), "")
Answers
-
You need individual INDEX/MATCHES to pull each of the dates individually. Then you can drop them both into the NETWORKDAYS function.
The base formula for an INDEX/MATCH would be
=INDEX({Start Date}, MATCH([Unique ID]@row, {Unique ID}, 0))
Then you would write it again and update the first range to pull the other date and finally drop both into your NETWORKDAYS function.
=NETWORKDAYS(INDEX({Start Date}, MATCH([Unique ID]@row, {Unique ID}, 0)), INDEX({End Date}, MATCH([Unique ID]@row, {Unique ID}, 0)))
-
Hi @Jerexify
Any reason you can't have the NETWORKDAYS formula in your two sheets? If you can, it would be ideal, as you can then do an INDEX/MATCH formula to get the index the column with networkdays and match it with the project ID and do a sum in your third sheet.
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Unfortunately we aren't able to do that based on the way the sheets are set up. With the amount of data in both, we are approaching the cell limit, so we can't add more to them.
-
So, I tried this, and I got INVALID DATA TYPE in some cells, and NO MATCH in others.
Since I'm referencing the dates of two sheets, would it be your formula, then replicated again with a + in between for the second sheet?
As in:
=NETWORKDAYS(INDEX({Sheet 1 Start Date}, MATCH([Unique ID]@row, {Unique ID}, 0)), INDEX({Sheet 1 End Date}, MATCH([Unique ID]@row, {Unique ID}, 0))) + NETWORKDAYS(INDEX({Sheet 2 Start Date}, MATCH([Unique ID]@row, {Unique ID}, 0)), INDEX({Sheet 2 End Date}, MATCH([Unique ID]@row, {Unique ID}, 0)))
Or is there a different way to do that?
-
Invalid data type indicates that one or more referenced columns are either not set as date type columns or have non-date type data in them. No match indicates just that. There is no match based on the ID.
Temporarily insert 4 date type columns in your target sheet and drop each of the INDEX/MATCH formulas in there to see if there is a pattern of some sort where some dates are being pulled and others are throwing errors.
-
So, I checked all columns, and they are all date columns, and none of them have non-date entries included in them. I used filters to verify, and set up columns like you stated, and they were able to pull the information. I even tried to just use those columns to then make my final formula, but I guess since the information in those columns is technically a formula itself, that doesn't work.
I'm not really sure what to do next.
-
Can you provide screenshots of the extra columns and the column properties? I have never had an issue using formula generated dates in other formulas and have even done something very similar where I used a combination of INDEX/MATCHes to pull in two separate dates for calculations on a different sheet.
-
They're work-related and sensitive, so I can't screenshot. I can tell you that this is the formula we're pulling in the assigned date with, from 4 separate sheets:
=IFERROR(INDEX({Sheet1 Assigned Date}, MATCH(PROJID@row, {Sheet1 PROJID}, 0)), "") + IFERROR(INDEX({Sheet2 Assigned Date}, MATCH(PROJID@row, {Sheet2 PROJID}, 0)), "") + IFERROR(INDEX({Sheet3 Assigned Date}, MATCH(PROJID@row, {Sheet3 PROJID}, 0)), "") + IFERROR(INDEX({Sheet4 Assigned Date}, MATCH(PROJID@row, {Sheet4 PROJID}, 0)), "")
And a similar one to pull int he completed date.
Both of these columns with the formulas mentioned above, as well as all the columns these formulas are pulling from, are Date columns.
-
There's the issue. Your formula is outputting as a text string. If you "add" "" anywhere, it automatically turns into a text string, so even though it is pulling a date in, it is converted to a text string on the back end by the outputs of the IFERRORs. You should nest each INDEX/MATCH into the second portion of the IFERROR to output data that is stored as date type data.
=IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Assigned Date}, MATCH(PROJID@row, {Sheet1 PROJID}, 0)), INDEX({Sheet2 Assigned Date}, MATCH(PROJID@row, {Sheet2 PROJID}, 0))), INDEX({Sheet3 Assigned Date}, MATCH(PROJID@row, {Sheet3 PROJID}, 0))), INDEX({Sheet4 Assigned Date}, MATCH(PROJID@row, {Sheet4 PROJID}, 0))), "")
-
AH! You're a genius!! Thank you so much! That did the trick. :D
-
Excellent. Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
