Network Days + If statements

Hello,

I'm trying to build a formula that will show the network days between a [Start Date] and an [Completion Date], but also 1) if [Start Date] is blank, then return a blank instead of an #Invalid Data Type error; and also 2) if [Completion Date] is Blank, the formula subtracts the [Start Date] from Today(), so that we have a running tally of network days during which the project is open (instead of blank), which then stops counting once [Completion Date] is entered.

I can get the components to work individually but I can't seem to work out the logic with the IF statements. I have also tried with IFERROR but also can't seem to work it out.

=IF(BLANK([Start Date]@row, ""), IF(Blank([Completion Date]@Row, NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))

Thank you in advance!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/26/24 Answer ✓

    Hey @Blake T

    Sorry, I didn't check your parentheses.

    =IF(ISBLANK([Start Date]@row), "", IF(ISBLANK([Completion Date]@row), NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Blake T

    The function you are looking for is ISBLANK

    =IF(ISBLANK([Start Date]@row, ""), IF(ISBLANK([Completion Date]@Row, NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))

    Will this work for you?

    Kelly

  • Thank you so much @Kelly Moore , that was quite the oversight on my part. Unfortunately that correction alone wasn't enough. I have been tweaking my parentheses but do not seem to be able to solve the #Imparseable error.

    =IF(ISBLANK([Start Date]@row, ""), IF(ISBLANK([Completion Date]@row, NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/26/24 Answer ✓

    Hey @Blake T

    Sorry, I didn't check your parentheses.

    =IF(ISBLANK([Start Date]@row), "", IF(ISBLANK([Completion Date]@row), NETWORKDAYS([Start Date]@row, TODAY()), NETWORKDAYS([Start Date]@row, [Completion Date]@row)))

    Will this work for you?

    Kelly

  • Ahhh, that worked! I can't thank you enough!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!