Problem: We have a big project due on April 15. I need to figure out how many work days until the project is due.
Strategy: If you work Monday through Friday, use NETWORKDAYS. If you have another work week, use NETWORKDAYS.INTL. Both functions allow you to specify a list of company holidays and will factor the holidays into the calculation.
You specify a start date, an end date, and a list of company holidays. Excel will calculate the number or work days including the beginning and ending date.
- In a blank range in your worksheet, enter the company holidays for this year. Be sure to include the year. Instead of 12/25, enter 12/25/2014. Say that you store this list in I3:I10.
- Enter the formula =NETWORKDAYS(C3,B3,$I$3:$I$10) in cell D3. Note that the argument containing the holidays should be an absolute reference with dollar signs.
- Copy the formula down for all projects.
-
Column D counts days excluding weekends & holidays.
Before Excel 2010, the NETWORKDAYS always assumed the weekend is Saturday and Sunday. If you have an alternate weekend, the NETWORKDAYS.INTL function will handle it.
- New in Excel 2010, you can set the weekend.
Column E in Fig 563 calculates a Monday-Saturday workweek with =NETWORKDAYS.INTL(C3,B3,11,$I$3:$I$10).
Be aware that Excel is counting both the beginning and ending date. From Monday 2/17 to Monday 2/24, the NETWORKDAYS is calculating six days. That may not be the best answer at 5PM on Monday 2/17.
Additional Details: Enter the holidays on another worksheet and name the range something like HOLIDAYS. You don't have to worry about inadvertently deleting a project and wiping out one of the holidays out to the right.
An alternate strategy to protect the holidays out in I is to select cells J3:J10. Enter =1 and then press Ctrl+Shift+Enter. This will create a lame array formula in column J. If anyone tries to delete a row from 3 to 10, Excel will refuse with the somewhat cryptic, "You Can Not Change Part Of An Array" message.
If you have a starting date and want to go out 15 work days from the starting date, take a look at the WORKDAY and WORKDAY.INTL functions.