NetworkDays

You can use the NETWORKDAYS function to calculate the number of workdays between 2 dates, even taking holidays into account. The worksheet below indicates tasks, with starting and due dates. So that NETWORKDAYS can consider holidays, I added a “Holiday” column to the worksheet, and listed the 2 holidays that occur this semester.

The syntax for the NETWORKDAYS function is

NETWORKDAYS(startdate, enddate, holidays)

Therefore, cell D2 of the worksheet would contain this formula:

NOTE: If the NetworkDays formula results in a NAME? error message, you need to install the Analysis Toolpak Add In. From the Excel menu, select Tools, AddIns. Select the Analyis Toolpak and click OK. Reenter your formula.

=NETWORKDAYS(B2,C2,F2:F3)

Cell B2 and cell C2 contain the dates you want “subtracted”, while cells F2:F3 contain the dates of the holidays.

The calculated worksheet appears below.

While you can use Autofill to copy the formula to the remaining cells, you will need to edit each subsequent formula and correct the cells containing the holidays. Excel tends to increment the cells by 1 when autofill is used and unless the formula is corrected, the results will be inaccurate.

Note cell D4 has incremented the cells containing the holidays by one when Autofill is used. Instead of F2:F3, Excel fills with F4:F5., resulting in an incorrect calculation in cell D4. The formula must be edited to reflect the cells that contain the dates of the holidays.

Cell D4 with the corrected formula.