Most valuable professional
  • Excel Book Excerpt

Excel Calculate Work Days

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

Calculate Work Days

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.

LE10000567.jpg 

Figure 556 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 that is 1 day or any two consecutive days, the NETWORKDAYS.INTL function will handle it.

LE10000568.jpg 

Figure 557 New in Excel 201, you can set the weekend.

Column E in Figure 556 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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: