NETWORKDAYS with IF

I have a NETWORKDAYS formula that will calculate work days between a begin and complete date, but often our projects are paused, so I need a formula that will subtract the net work days during the pause.

I figured that out, as well:

=IF([Completed Date]@row = "", "", NETWORKDAYS([Begin Date]@row, [Completed Date]@row) - [Total Pause]@row)

but now I get an "INVALID" if there is a Completed Date but no Total Pause entry.

I tried this:

=IF([Completed Date]@row = "", "", NETWORKDAYS([Begin Date]@row, [Completed Date]@row) - IF([Total Pause]@row > 0, NETWORKDAYS([Begin Date]@row, [Completed Date]@row) - [Total Pause]@row, ""))

But it only returns the Total Pause entry. If I change that - before the second IF to a , I get Incorrect Argument Set. Also, if there is no Total Pause entry, it's "Invalid operation".

Any ideas?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Give this one a whirl...

=IF([Completed Date]@row = "", "", NETWORKDAYS([Begin Date]@row, [Completed Date]@row) - IF([Total Pause]@row <> "", [Total Pause]@row, 0))

Basically we replace [Total Pause]@row with an IF statement that says if it is not blank then use it, but if it is blank use zero.

Paul Newcome

Happy to help. 👍️

All comments

Paul Newcome

So if there is no completed date and no total pause, what would be the output?

What if there is no completed date but there is a total pause?

SueSus

Thank you for responding!

It's an ongoing tracking sheet. Some rows don't even have Begin dates.

If there's no completed date and no total pause yet, the duration should remain blank.

If there's a completed date, but the project was never paused, the duration should calculate the amount of work days between Begin date and Completed date. - My formula is giving me an error if I don't have an entry in Total Pause.

If there is a pause, it should calculate the net work days between Begin date and completed date and subtract the total pause.

SueSus

Sorry, I left off the last part of your question.

If there is a total pause, but no completed date yet, the duration should remain blank

Paul Newcome

Give this one a whirl...

=IF([Completed Date]@row = "", "", NETWORKDAYS([Begin Date]@row, [Completed Date]@row) - IF([Total Pause]@row <> "", [Total Pause]@row, 0))

Basically we replace [Total Pause]@row with an IF statement that says if it is not blank then use it, but if it is blank use zero.

SueSus

It worked!!

Thank you! I have worked on that for HOURS! Someday I hope to be as good as you!!

Thank you again

Paul Newcome

Happy to 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.