Excel: Calculate Work Days

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. 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.
  2. 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.
  3. Copy the formula down for all projects.

  1. 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.

  2. 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.