Difficulties Using ISBLANK Function

Options

Hello,

I am curious what I need I need to do, am relatively new to SS and it's functions. We review products, and often times this analysis requires the product to be sent out to a third-party for analysis. I do not want these third-party days (business days only) counted against the lab's time to complete. What formula do I need to use to accomplish this? Please see below, and note how row 3 gives me #INVALID when the product didn't need to be sent out to a third-party for analysis. Appreciate your time and any help you can provide.

Thanks,

AK


Best Answer

  • Mike Raposo
    Mike Raposo ✭✭✭✭
    Answer ✓
    Options

    Hi Austen,


    I believe this formula should accomplish what you're looking for:


    =IF(OR([Third-Party Date Out]@row = "", [Third-Party Date In]@row = ""), 0, NETWORKDAYS([Third-Party Date Out]@row, [Third-Party Date In]@row))


    Also just as a tip you can use ="" as an operator instead of the formula ISBLANK().


    Hope this helps! Let me know if you have any questions or are still having issues.


    Best,

    Mike

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Austin, You didn't share your column formulas. I assume you need to use an IF(ISBLANK(..) or IFERROR(..) formula to look for 3rd party lab days without returning an error and then calculate NETWORKDAYS. If you share your column formulas I can assist further. Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Austin Kuhn
    Austin Kuhn ✭✭✭
    edited 11/12/20
    Options

    Hi Mark,

    I'd like "Destructive Testing Pending Days" to be "0" if there are no dates input either the "Third-Party Date Out" or "Third-Party Date In" Columns. Else, I'd like "Destructive Testing Days" to be the number of working days between columns "Third-Party Date Out" and "Third-Party Date In" columns. Think I need some kind of IF/ISBLANK/ELSE statement or something similar. Think I'm pretty far off the mark here, but hopefully my description helps aid in what I'm trying to accomplish. Appreciate your time and help.

    Thanks,

    Austin

  • Mike Raposo
    Mike Raposo ✭✭✭✭
    Answer ✓
    Options

    Hi Austen,


    I believe this formula should accomplish what you're looking for:


    =IF(OR([Third-Party Date Out]@row = "", [Third-Party Date In]@row = ""), 0, NETWORKDAYS([Third-Party Date Out]@row, [Third-Party Date In]@row))


    Also just as a tip you can use ="" as an operator instead of the formula ISBLANK().


    Hope this helps! Let me know if you have any questions or are still having issues.


    Best,

    Mike

  • Austin Kuhn
    Austin Kuhn ✭✭✭
    Options

    Mike,

    That works perfectly and does exactly what I wanted. I am going to look over this function to try and understand it better for future operations. Do appreciate your help, and wish you the best.

    Thanks,

    Austin

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!