The NETWORKDAYS function counts only weekdays between two dates

Excel has a built-in function called NETWORKDAYS that counts the number of working days between two dates, excluding weekends automatically. This is faster and more accurate than trying to count manually or using a formula that treats every day the same.

The basic formula looks like this: =NETWORKDAYS(start_date, end_date). You give it a beginning date and an ending date, and it returns how many business days fall between them — Monday through Friday only.

This matters because payroll, project timelines, and delivery schedules all run on business days, not calendar days. A task that takes 5 business days is not the same as a task that takes 5 calendar days if a weekend falls in between.

Key Takeaways

  • NETWORKDAYS counts weekdays only and excludes Saturday and Sunday by default, so you do not have to subtract weekends manually.
  • The formula syntax is =NETWORKDAYS(start_date, end_date), where both dates can be cell references or dates you type directly.
  • You can add a third argument to exclude specific holidays that your business observes, so the count reflects actual working days.
  • NETWORKDAYS.INTL lets you define which days count as weekends if your business operates on a different schedule than Monday through Friday.

Setting up the basic NETWORKDAYS formula

Open your spreadsheet and click on the cell where you want the business day count to appear. Type the formula starting with an equals sign: =NETWORKDAYS(. Then click on the cell containing your start date, type a comma, click on the cell containing your end date, and close the parenthesis with ).

For example, if your start date is in cell A2 and your end date is in cell B2, you would type =NETWORKDAYS(A2,B2) and press Enter. Excel calculates the result when ready. If the dates are January 15 (Monday) and January 19 (Friday), the result is 5. If the end date is January 20 (Saturday), the result is still 5 because Saturday does not count.

You can also type dates directly into the formula if you prefer. The formula =NETWORKDAYS("1/15/2024","1/19/2024") works the same way. Use whichever method matches how your data is organized.

Excluding holidays from the count

Most businesses do not work on holidays, so NETWORKDAYS can exclude them. Add a third argument to your formula listing which dates are holidays. The syntax becomes =NETWORKDAYS(start_date, end_date, holiday_range).

Create a list of your company's holidays in a separate column — for example, cells D2 through D10 might contain January 1, July 4, November 28, and so on. Then modify your formula to =NETWORKDAYS(A2,B2,D2:D10). Now the count skips any business days that fall on those holiday dates.

If you have only one or two holidays in your range, you can list them directly: =NETWORKDAYS(A2,B2,"7/4/2024","12/25/2024"). For a longer list, keeping holidays in their own column is cleaner and easier to update year to year.

Using NETWORKDAYS.INTL for non-standard work weeks

Some businesses do not operate Monday through Friday. Retail stores, hospitals, and customer service centers often work weekends. The NETWORKDAYS.INTL function lets you define which days count as weekends.

The formula is =NETWORKDAYS.INTL(start_date, end_date, weekend_code). The weekend code is a number from 1 to 17 that tells Excel which days to exclude. Code 1 (the default) means Saturday and Sunday. Code 2 means Sunday and Monday. Code 11 means Saturday only. Code 12 means Sunday only.

If your business is closed only on Sundays, use =NETWORKDAYS.INTL(A2,B2,12). If you need to exclude both weekends and holidays, add a fourth argument: =NETWORKDAYS.INTL(A2,B2,12,D2:D10). The full list of codes is available in Excel's help menu if you need a schedule other than the common ones.

Calculating days remaining until a important date

You can use NETWORKDAYS to count how many business days remain before a project is due. If today is in cell A1 and the important date is in cell B1, the formula =NETWORKDAYS(A1,B1) tells you how many working days you have left.

To make this update automatically, use the TODAY() function instead of typing a date. The formula =NETWORKDAYS(TODAY(),B1) always calculates from today's date, so the remaining days decrease by one each business day. This is useful in project tracking sheets where you want a live count without updating manually.

If the result is negative — meaning the important date has passed — you can wrap the formula in an IF statement to display a message instead: =IF(NETWORKDAYS(TODAY(),B1)<0,"Overdue",NETWORKDAYS(TODAY(),B1)). This shows "Overdue" if the important date is in the past, or the number of days remaining if it is still ahead.

Common mistakes and how to fix them

The most frequent error is forgetting that NETWORKDAYS counts both the start and end dates if they are weekdays. If you want to count only the days between two dates (not including the dates themselves), subtract 1 from the result: =NETWORKDAYS(A2,B2)-1. Check your project requirements to see whether the start and end days should be included.

Another mistake is entering dates in a format Excel does not recognize. If your formula returns an error, make sure dates are in a standard format like MM/DD/YYYY or use the DATE function: =NETWORKDAYS(DATE(2024,1,15),DATE(2024,1,19)). This removes any ambiguity about how Excel should read the date.

If your holiday list is in a different sheet, include the sheet name in the range: =NETWORKDAYS(A2,B2,Holidays!D2:D10). If the formula still shows an error, check that your holiday cells contain actual dates, not text that looks like dates.

Comparing NETWORKDAYS to other date functions

Excel has other date functions that serve different purposes. DAYS counts all calendar days including weekends. DATEDIF counts days, months, or years between two dates but does not exclude weekends. NETWORKDAYS is the only function built specifically for business day calculations.

If you need to count business hours instead of business days — for example, how many 8-hour work days fit in a important date — multiply the NETWORKDAYS result by 8: =NETWORKDAYS(A2,B2)*8. This gives you a rough estimate, though it does not account for partial days or hours worked outside standard business hours.

Frequently Asked Questions

Does NETWORKDAYS count the start date and end date?

Yes, both dates are included in the count if they fall on weekdays. If your start date is Monday and your end date is Friday of the same week, the result is 5 days. If you want to exclude one or both dates, subtract 1 or 2 from the formula result.

What happens if I enter the end date before the start date?

NETWORKDAYS returns a negative number. This is useful if you want to show how many days overdue something is. If you want to avoid negative numbers, wrap the formula in ABS to show the absolute value: =ABS(NETWORKDAYS(A2,B2)).

Can I use NETWORKDAYS with times, not just dates?

NETWORKDAYS ignores the time portion of a cell and counts only the date. If you need to calculate business hours or minutes, you will need a more complex formula that subtracts times within each business day separately.

How do I copy a NETWORKDAYS formula to multiple rows?

Click the cell containing your formula, then drag the small square at the bottom right corner down to the rows below. Excel automatically adjusts the cell references (A2 becomes A3, A4, and so on) while keeping the holiday range the same if you used absolute references like D$2:D$10.

What if my company observes holidays that fall on weekends?

Include them in your holiday list anyway. NETWORKDAYS will not double-count them — if a holiday falls on Saturday, it is already excluded as a weekend, so adding it to the holiday list does not change the result.