NETWORKDAYS with IF

Hi
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?
Best Answers
-
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.
-
Answers
-
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?
-
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.
-
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
-
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.
-
It worked!!
Thank you! I have worked on that for HOURS! Someday I hope to be as good as you!!
Thank you again
-
Help Article Resources
Categories
Check out the Formula Handbook template!