Networkday formula with Cross Sheet Reference to Holidays Sheet

Options
Jonathan Rinkerman
Jonathan Rinkerman ✭✭✭
edited 01/10/22 in Formulas and Functions

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})

Please let me know what I am missing.

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/10/22
    Options

    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.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jonathan Rinkerman
    Options

    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
    DiBrown ✭✭
    Options

    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
    DiBrown ✭✭
    Options

    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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!