NETWORKDAYS with IF

SueSus
SueSus ✭✭
edited 03/26/21 in Formulas and Functions

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?

Tags:

Best Answers

Answers

  • Paul Newcome
    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
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    Happy to help. πŸ‘οΈ

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!