RE; invalid data type when adding Holidays
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

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

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
Categories
Check out the Formula Handbook template!