Calculate Work Days
June 30, 2022 - by Bill Jelen

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.

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

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