MrExcel.com - Your One Stop for Excel Tips & Solutions

Calculate Work Days

June 30, 2022 - by

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.

To count weekdays, use =NETWORKDAYS(C3,B3,I3:I10) where the company holidays are in column I.
Figure 548. Column D counts days excluding weekends & holidays.

The NETWORKDAYS always assumed the weekend is Saturday and Sunday. If you have an alternate weekend, the NETWORKDAYS.INTL function will handle it.

The new NETWORKDAYS.INTl lets you specify a weekend as any two consecutive weekdays or a single weekday.
Figure 549. You can set the weekend.

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

This article is an excerpt from Power Excel With MrExcel

Title photo by Chris Barbalis on Unsplash