Networkday formula with Cross Sheet Reference to Holidays Sheet

Jonathan Rinkerman · 01/10/22

Hello!

I'm running into a strange issue with my networkday formula. I'm trying to keep track of time off requests as they are submitted. The following formula was working for me as of now.

=NETWORKDAY([Start Date]@row, [End Date]@row)

However, I noticed that the count wasn't correct as some people were including company holidays In their submissions which shouldn't count against that number. I added our company holidays to the same sheet and excluded them and that worked.

I then wanted to clean things up a bit and create a separate sheet for company holidays and just use a reference in the request tracker. However, when I use a cross sheet reference in the formula I get a # invalid data type error. The formula is below and here is a screenshot to the referenced sheet.

=NETWORKDAY([Start Date]@row, [End Date]@row, {Company Holidays 2022 Range 2})

Screen Shot 2022-01-10 at 11.16.49 AM.png

Please let me know what I am missing.

Jeff Reisman · 01/10/22

All your date columns are actual Date type columns, yes? Do you have any values in those columns that are not dates? Those would be the only things that should cause an #INVALID DATA TYPE error.

I tested this out using a Date column as start date, a system "Created" date column as the end date, and a reference to an external holiday list (in a Date column) and got the expected result.

Jonathan Rinkerman · 01/11/22

Boy do I feel dumb! The column referenced with the dates was actually a text column. Once I made that change the formula worked.

Thank you!

DiBrown · 02/19/22

I'm having the same problem. But the column in my holiday table IS formatted as a date. I'm getting an Invalid Data Type Error. Here is my formula...=NETWORKDAYS([Time Period Start]@row - [Time Period End]@row, {Holidays}). I have double-checked several times and it's definitely a date field! I'm going to try restarting Smartsheets because I feel like something is wrong.

DiBrown · 02/19/22

https://community.smartsheet.com/discussion/comment/320194#Comment_320194

I found my issue! It WAS the format... even though I had it set to Date, I had copied and pasted from Excel and it was just not working. When I manually retyped all the dates, it worked!

Jeff Reisman · 02/21/22

https://community.smartsheet.com/discussion/comment/320196#Comment_320196

Glad you found the answer.

In the future, instead of manually retyping all the dates, you can use a formula to rebuild the date values in date format. Say for instance you have a column of text-formatted dates, such as 01/01/2022. Copy your data to another column, and in your original date-type column, reference the copied data:

=DATE(YEAR(RIGHT(CopiedDates@row, 4)), MONTH(LEFT(CopiedDates@row, 2)), DAY(MID(CopiedDates@row, 4, 2)))

In English: Build a date value, with Year being the first 4 characters from the right, Month being the first 2 characters starting from the left, and Day being the 2 characters starting at the 4th character from the left.

Networkday formula with Cross Sheet Reference to Holidays Sheet

Answers

Help Article Resources

Categories