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)
OMG!!! Its very tricky for me. You did really a great job providing here. I will definitely try this.