Returning a BLANK cell with IFERROR

Options

Good Morning.

I am trying to make this formula return a blank cell instead of #INVALIDDATATYPE if not using the IFERROR syntex or #UNPARESABLE how I currently have it built. What am I missing?


=NETDAYS([Projected Delivery]@row, [Cleared RCI]@row) - 1, IFERROR(@cell), 0)


Thank you!

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    edited 02/09/22 Answer ✓
    Options

    Hi @kblood ,

    Try nesting the formula within the IFERROR:

    =IFERROR(NETDAYS([Projected Delivery]@row, [Cleared RCI]@row)-1,"")

    This basically tells Smartsheet to run the NETDAYS-1 formula, but if there's an error, display a blank cell.


    Hope this helps. Let me know if it works!

    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    edited 02/09/22 Answer ✓
    Options

    Hi @kblood ,

    Try nesting the formula within the IFERROR:

    =IFERROR(NETDAYS([Projected Delivery]@row, [Cleared RCI]@row)-1,"")

    This basically tells Smartsheet to run the NETDAYS-1 formula, but if there's an error, display a blank cell.


    Hope this helps. Let me know if it works!

    Best,

    Heather

  • kblood
    Options

    That did exactly it!! I have such a hard time knowing the proper order to build a formula. Thank you so much!!!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    @kblood Happy to help! The order of formulas definitely makes a huge impact and can be tricky to set up.

    With IFERROR, I always have to think of it as the following:

    =IFERROR(Formula, What to do if the formula fails)

    Generally speaking, my IFERRORS are almost always the furthest-exterior (if that makes sense) function in the formula; however, there are some more complex scenarios in which the IFERROR(formula, what to do if the formula fails) is nested within another formula.

  • Melanie Myers
    Options

    Hello, Where would I add the IFERROR, so I can have it show a blank when there isn't a date, instead of #INVALIDDATATYPE ?

    =IF(MONTH([Due date]@row) < 4, "Q1", IF(MONTH([Due date]@row) < 7, "Q2", IF(MONTH([Due date]@row) < 10, "Q3", "Q4")))

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Melanie Myers

    I would actually use ISDATE in this scenario, so you could add an IF statement at the front of your formula like so:

    =IF(ISDATE([Due Date]@row), the rest of your formula, otherwise "")

    or:

    =IF(ISDATE([Due Date]@row), IF(MONTH([Due Date]@row) < 4, "Q1", IF(MONTH([Due Date]@row) < 7, "Q2", IF(MONTH([Due Date]@row) < 10, "Q3", "Q4"))), "")

    Cheers,

    Genevieve

  • Melanie Myers
    Options

    Thank you so much, Genevieve! This worked beautifully!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!