RE; invalid data type when adding Holidays

Options

Hello,

I am trying to add public holidays to a networkdays formulla. The formula works well when populated with dates, but display invalid data type when blank. see attached formulla and list of public holidays which i referenced from another sheet. any help would be grateful so that the invalid data not displayed.


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Vinton Douglas

    An IF statement will allow you to first evaluate if you have dates before trying to apply the formula.

    =IF(AND(ISDATE([Vacation Start]@row), ISDATE([Vacation End]@row)),NETWORKDAYS([Vacation Start]@row, [Vacation End]@row, {Public Holidays 2022}))

    The formula above will execute your original formula when both Vacation Start and Vacation End are populated. It will return a blank cell when either are blank. If you had an alternate formula you wanted to run in the case of blank cells, we could build a Nested IF to do that. Let me know if you wish to do this and need any help.

    Does the above give you your expected result?

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Vinton Douglas

    An IF statement will allow you to first evaluate if you have dates before trying to apply the formula.

    =IF(AND(ISDATE([Vacation Start]@row), ISDATE([Vacation End]@row)),NETWORKDAYS([Vacation Start]@row, [Vacation End]@row, {Public Holidays 2022}))

    The formula above will execute your original formula when both Vacation Start and Vacation End are populated. It will return a blank cell when either are blank. If you had an alternate formula you wanted to run in the case of blank cells, we could build a Nested IF to do that. Let me know if you wish to do this and need any help.

    Does the above give you your expected result?

    cheers

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!