Why is December's NETWORKDAYS formula not calculating correctly?

Trang Turtletraxx

Hi - I have this formula that works perfectly for the year, but when I get to December it is calculating an odd negative number. Can anyone help me understand why and what I need to do to correct it?

Paul Newcome

Exactly.

=IF([Team Member / Projects]@row = "", "", NETWORKDAYS([December Start Date]@row, [December End Date]@row, {Metadata | Holidays}) * 8)

Paul Newcome

The second DATE function should have one added to the year.

IFERROR(DATE(…………………..), DATE(YEAR([December Start Date]@row) + 1, 1, 1)), …………………….)

Trang Turtletraxx

@Paul Newcome I added the +1 to the second DATE function, but it still isn't calculating correctly. The NETWORKDAYS [holiday] portion is linked to our company's list of holidays. There is only 1 holiday in December. With 22 workdays, minus the 1 holiday, times 8 hours should total to 168 hours, but the updated formula with the +1 is totaling to 176.

Paul Newcome

Your formula is setting the end date as the first of the following month which would be Jan 1 instead of Dec 31.

Is there a reason you are using this calculation as opposed to just referencing the [December End Date] column the same way you are referencing the [December Start Date] column?

Trang Turtletraxx

I just copied and pasted the formula I used for the other months. Should all the months be referencing the End Date vs the Start Date? So for December, I just need to change Start with End?

Trang Turtletraxx

Oh I see what you're saying. Going back to the basic formula structure of referencing Start, End, Holiday. For some reason, in my formula sheet, I had what is in the screen shots to determine the Work Day hours. Not sure why it's all drawn out that way.

Paul Newcome

Exactly.

=IF([Team Member / Projects]@row = "", "", NETWORKDAYS([December Start Date]@row, [December End Date]@row, {Metadata | Holidays}) * 8)

Trang Turtletraxx

Going back to the basics worked perfectly. I'll have to update my formula sheet. Thanks for your help!

Paul Newcome

Happy to help. 👍️