- 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.
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.
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.
For more resources for Microsoft Excel: