NETWORKDAY Not Working With Columns With Formula

Lea722

Hello :)

I'm calculating the difference between Bethany's Start Date and Bethany's Updated Start Date.

The "Bethany's Updated Start Date" column has a formula.

When I use NETWORKDAY, it returns an invalid value and I think this is because the "Bethany's Updated Start Date" has a formula in it. I tried wrapping the formula in an iferror, but that only returned a blank cell.

Any ideas? Thank you!!

Paul Newcome

The issue is that your formula is outputting a text string that only looks like a date value. Try making an adjustment to that one by nesting them instead of adding them.

Basically you would take the second INDEX/COLLECT and drop it into the output of the first IFERROR. Then drop the second INDEX/COLLECT into the output of the second IFERROR, so on and so forth.

=IFERROR(INDEX(COLLECT(………………….), 1), IFERROR(INDEX(COLLECT(…………………….), 1), IFERROR(INDEX(COLLECT(………………….), 1), ………………………………

Paul Newcome

What exactly is the formula in the updated date column?

Lea722

Hi @Paul Newcome!

The formula in the updated date column is pulling in info from another sheet to get the updated start date should there be one:

=IFERROR(INDEX(COLLECT({Bethany's Updated Start Date}, {Bethany's Tracker}, HAS(@cell, [Who should work on this task first?]@row)), 1), "") + IFERROR(INDEX(COLLECT({Bethany's Updated Start Date}, {Bethany's Tracker}, HAS(@cell, [Who should work on this task second?]@row)), 1), "") + IFERROR(INDEX(COLLECT({Bethany's Updated Start Date}, {Bethany's Tracker}, HAS(@cell, [Who should work on this task third?]@row)), 1), "") + IFERROR(INDEX(COLLECT({Bethany's Updated Start Date}, {Bethany's Tracker}, HAS(@cell, [Who should work on this task fourth?]@row)), 1), "")

Paul Newcome

The issue is that your formula is outputting a text string that only looks like a date value. Try making an adjustment to that one by nesting them instead of adding them.

Basically you would take the second INDEX/COLLECT and drop it into the output of the first IFERROR. Then drop the second INDEX/COLLECT into the output of the second IFERROR, so on and so forth.

=IFERROR(INDEX(COLLECT(………………….), 1), IFERROR(INDEX(COLLECT(…………………….), 1), IFERROR(INDEX(COLLECT(………………….), 1), ………………………………

Lea722

This all worked like a charm. Thank you so much @Paul Newcome!

Paul Newcome

Happy to help. 👍️