Including Holidays

Maybe someone can help me figure this out. WHen i use the below formula I get a number in return or a blank if there is missing data in the assignment form approved date or the disposition date fields. so the formula is exactly how i want it. My problem however is when I add holidays

=IFERROR(NETWORKDAYS([Assignment Form Approved Date]@row, [Disposition date]@row), " ")

If i add the holiday column the return is a blank in every row

=IFERROR(NETWORKDAYS([Assignment Form Approved Date]@row, [Disposition date]@row), Holidays:Holidays)," ")

help?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you remove the IFERROR and then provide the error it is throwing?

  • Hi Paul,

    So if I remove IFERROR the error i get is #UNPARSEABLE

    =NETWORKDAYS([Assignment Form Approved Date]@row, [Disposition date]@row, Holidays:Holidays), " ")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will also need to remove the rest of the IFERROR statement at the end. What do you get with this...

    =NETWORKDAYS([Assignment Form Approved Date]@row, [Disposition date]@row, Holidays:Holidays)


    I also notice that's not quite the same as you original post. Removing the entire IFERROR from your original formula leaves you with

    NETWORKDAYS([Assignment Form Approved Date]@row, [Disposition date]@row), Holidays:Holidays)

    which has an extra closing parenthesis after [Disposition Date]@row that needs removed.

  • The formula work great if i have data in those columns (assignment form approved date and the disposition date). When tere is no data to calculate I get the #invalid data type error, which is why I was trying to the IFERROR - but then that's giving me blank entries.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That would be expected when using the NETWORKDAYS function. You need a start and end date for the function to calculate.


    This should work.

    =IFERROR(NETWORKDAYS([Assignment Form Approved Date]@row, [Disposition date]@row, Holidays:Holidays), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!