Help with NETWORKDAYS.INTL function

Question

I am trying to calculate the number of workdays between 27/03/2023 and 11/04/2023 considering the 2nd and 4th Saturday of the month and every Sunday as holidays using NETWORKDAYS.INTL function in Excel 2016.

However, the function keeps returning the result of 14 when it should be 13 according to my manual calculation. I understand that the function counts start and end dates, but shouldn't it be 13? Can someone please tell me why this is happening and how I can fix it?

Below is the screenshot of the same (Note: I have used 11 for "Sunday only" weekend and specified 8/04/2023 as a holiday since that is the only 2nd/4th Saturday between the two dates):

Answer 1

That's odd indeed. I tried your scenario and when you hard-code the holiday date it comes out with 14. However, when you enter the holiday in a cell and refer to it inside the formula, it arrives to 13. See picture below. Don't really understand why.

Answer 2

Thank you for your reply.

I tried what you did and it works, at least it solves the problem. Anyway, I hope someone can explain why entering the holiday as a date function within networkdays.intl function is returning the incorrect answer.

Answer 3

Hello Shakiru,

Thank you for your reply.

I understand that the function counts start and end dates and that is exactly what I want, however, even after that, the result should be 13 instead of 14. If one manually counts the number of working days between the two dates (including start and end dates) according to the conditions mentioned in the original question, it comes out to 13.

I hope you understand what I am trying to say.

Answer 4

HI H.ZA!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

The reason why the NETWORKDAYS. INTL function is returning 14 instead of 13 is because it is including both the start and end dates in its calculation. So, if you want to exclude these dates from your calculation, you need to subtract 2 from the result.

To exclude these dates from the calculation, you need to subtract 2, like this:

=NETWORKDAYS. INTL(A1,B1,11,DATE(2023,4,8)-2)

This will give you a result of 13 workdays, which is the correct number of workdays between the two dates.

Please, remember to give back to the community. Help the next person with this problem by rating and indicating if this conversation was helpful. Thank you

Best Regards, Shakiru