Sometimes, you may need to calculate the number of workdays between two dates in Excel, such as the duration of a project, the elapsed time of a task, or the number of business days in a month. In this article, we will show you how to use the NETWORKDAYS function and the NETWORKDAYS.INTL function to perform this calculation. We will also explain the basic theory behind these functions, the procedures to use them, and some examples with real data.
The NETWORKDAYS function returns the number of whole workdays between two dates, excluding weekends and holidays. The syntax of the function is:
=NETWORKDAYS(start_date, end_date, [holidays])
start_dateandend_dateare the dates that define the start and end of the period. They can be entered as date values, cell references, or text strings.[holidays]is an optional argument that specifies a range of cells that contain the dates of holidays. If omitted, the function assumes no holidays.
The NETWORKDAYS function assumes that the standard workweek is from Monday to Friday, and that weekends are Saturday and Sunday. However, if you have a different workweek or weekend, you can use the NETWORKDAYS.INTL function instead. The syntax of the function is:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
start_dateandend_dateare the same as in theNETWORKDAYSfunction.[weekend]is an optional argument that specifies which days are considered weekends. It can be entered as a number from 1 to 7, or a string of seven 0s and 1s, where 1 represents a non-workday and 0 represents a workday. For example, “0000011” means Saturday and Sunday are weekends, while “0110000” means Thursday and Friday are weekends. If omitted, the function assumes “0000011” as the default value.[holidays]is the same as in theNETWORKDAYSfunction.
Procedures
To use the NETWORKDAYS function or the NETWORKDAYS.INTL function, follow these steps:
- Enter the start date and the end date in two cells, such as A2 and B2.
- Enter the dates of holidays in a range of cells, such as C2:C5. This step is optional, but recommended if you want to exclude holidays from the calculation.
- In another cell, such as D2, enter the formula
=NETWORKDAYS(A2, B2, C2:C5)or=NETWORKDAYS.INTL(A2, B2, [weekend], C2:C5), depending on your workweek and weekend preferences. Replace[weekend]with the appropriate number or string, if needed. - Press Enter to get the result.
Example
Let’s say you want to calculate the number of workdays between January 1, 2024 and January 31, 2024, excluding weekends and the following holidays: January 1 (New Year’s Day), January 15 (Martin Luther King Jr. Day), and January 29 (Presidents’ Day). You can use the NETWORKDAYS function or the NETWORKDAYS.INTL function to get the answer. Here is an example of how the Excel table would look like:
| Start Date | End Date | Holidays | Result |
|---|---|---|---|
| 1/1/2024 | 1/31/2024 | 1/1/2024 | 19 |
| 1/15/2024 | |||
| 1/29/2024 |
The formula in cell D2 is =NETWORKDAYS(A2, B2, C2:C4), which returns 19 as the number of workdays between January 1, 2024 and January 31, 2024, excluding weekends and the three holidays.
Other Approaches
If you do not want to use the NETWORKDAYS function or the NETWORKDAYS.INTL function, you can also calculate the number of workdays between two dates using other methods, such as:
- Subtracting the number of weekends and holidays from the total number of days between two dates. For example, if the start date is A2 and the end date is B2, you can use the formula
=(B2-A2+1)-((WEEKDAY(B2)-WEEKDAY(A2)+1)/7*2)-COUNTIF(C2:C4,A2:B2), where C2:C4 is the range of cells that contain the dates of holidays. This formula works for the standard workweek and weekend, but you may need to adjust it for different scenarios. - Using a helper column that marks the workdays and non-workdays with 1s and 0s, and then summing up the values. For example, if the start date is A2 and the end date is B2, you can use the formula
=IF(AND(WEEKDAY(C2,2)<=5,ISNA(MATCH(C2,$D$2:$D$4,0))),1,0)in cell E2, where C2 is the date column and D2:D4 is the range of cells that contain the dates of holidays. Then, copy the formula down to the end of the date column, and use the formula=SUM(E2:E32)in another cell to get the result. This method allows you to customize the workdays and non-workdays with different criteria.
