Tips and Tricks 142 - Determine Number of Working Days in a Year

By |Saturday, August 06th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , |1 Comment

Suppose, you have been given a year in A1 (Say A1 = 2016) and you need to determine the number of working days in a Year, then your formula to determine number of working days would be -

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1)

The above formula is based on the fact that Saturdays and Sundays are weekends. Starting Excel 2010, you can control the weekends in the formula and function is NETWORKDAYS.INTL

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110")

In the string "000110" - First digit is Monday and last digit is Sunday. 1 defines that particular day as weekend.

If you have got your list of holidays in a range say B1:B20 (B1:B20 should contain dates in date format), you can have following formulas

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1,B1:B20)

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110",B1:B20)