RE; invalid data type when adding Holidays

Vinton Douglas

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.

Invalid data type.png

Holidays.png

Kelly Moore

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

Kelly Moore

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