Regarding the holidays, you need to have a sheet or table in a worksheet defining the dates where it is declared a holiday. Excel will not know which date is declared a holiday unless you manually enter them.
For my sample, I picked US holidays from this link:http://www.timeanddate.com/holidays/us/
Now, in your Excel file, you must have a sheet or table enumerating these holidays. Then, in your formula, like in B2 or E2, you add another parameter for the holiday.
If the holiday is in another sheet, the B2 formula will then become =WORKDAY(B2,5,Holidays!B3:B170)
In this sample, Holidays
is the name of my sheet containing the holidays, and B3:B170
is the range containing the dates itself.
If you need to copy the formula in adjacent cells, you need to make the Holiday parameter static like this:=WORKDAY(B2,5,Holidays!$B$3:$B$170)
This means that even if you drag the formula below to copy it to the other cells, the holiday cell range (Holidays!$B$3:$B$170) will not change.
Here is my sample file as your pattern:http://wikisend.com/download/597836/sample-workday-holidays.xlsx
Sheet 1 there has my formula and the Holidays sheet is the one I am referring to as the table where the holidays are declared.
Please remember to rate my service positively (3-5 stars/faces)
once you have all the information you need. Tips
are always highly appreciated!
If you need assistance in the future, you can request me any time.