To calculate the number of workdays between two dates in Excel, use =NETWORKDAYS(A2,B2), with the start date in A2 and the end date in B2. It counts Monday through Friday and includes both endpoint dates when they are workdays; you can also supply a holiday list or use a different weekend pattern.
What Excel Counts as a Workday
Excel’s NETWORKDAYS function counts whole working days in a date range. By default, Saturday and Sunday are weekends, while Monday through Friday are potential workdays. A date that falls on a weekend is not counted, even if it is the first or last date in the range. Holidays are counted as weekdays unless you provide them separately.
The word “between” can mean different things in everyday conversation. NETWORKDAYS includes both the start and end dates if each is a weekday and is not listed as a holiday. For example, Monday through Friday in the same week produces five workdays. Tuesday through Thursday produces three. If the range begins on Saturday and ends on Sunday, the result is zero, because neither endpoint is a workday.
This is a count of calendar dates, not a calculation of hours worked. It does not account for half-days, varying shifts, or whether an employee actually worked on a particular weekday. Those require additional data and a more tailored calculation.
Basic NETWORKDAYS Formula
With the starting date in A2 and the ending date in B2, enter this formula in another cell:
=NETWORKDAYS(A2,B2)
For example, if A2 contains January 8, 2024, a Monday, and B2 contains January 12, 2024, a Friday, the result is 5. The calculation includes Monday, Tuesday, Wednesday, Thursday, and Friday. If B2 instead contains Saturday, January 13, the result remains 5 because Saturday is excluded.
To try it in Microsoft Excel for Microsoft 365 on Windows:
- Enter a valid start date in A2 and end date in B2.
- Select the cell where you want the count to appear.
- Type
=NETWORKDAYS(A2,B2)and press Enter. - Change either date to see how the count changes.
If Excel displays a date rather than a whole number, the formula cell may have a date format. Select the result cell and choose Home > Number Format, then select General or Number. The underlying result is still a count; the cell format controls how it appears.
Include Holidays in the Calculation
NETWORKDAYS accepts an optional holiday argument. Put holiday dates in a range, such as D2:D10, and provide that range as the third argument. Each matching date is removed from the count if it would otherwise be a weekday.
With the start date in A2, the end date in B2, and holiday dates in D2:D10, use:
=NETWORKDAYS(A2,B2,D2:D10)
For a concrete example, suppose A2 is Monday, January 8, 2024, B2 is Friday, January 12, and D2 contains Wednesday, January 10. If the remaining holiday cells are blank, the result is 4: the five weekdays in the range minus the listed Wednesday holiday. A holiday on a Saturday would not reduce the count, because that date was already excluded as a weekend.
Enter holidays as real Excel dates, not text that only looks like a date. Keep the list in a dedicated column and use an absolute reference such as $D$2:$D$10 if you plan to copy the formula down. That keeps the holiday range fixed while the start and end references adjust by row. Blank cells in a holiday range are generally ignored, so it is fine to leave unused cells empty.
Use a Different Weekend Schedule
If your workweek does not follow the standard Monday-to-Friday schedule, use NETWORKDAYS.INTL. It lets you specify which days are weekends while still allowing a holiday list. This is useful for schedules such as Sunday through Thursday, or for a business that closes only on Sunday.
With start and end dates in A2 and B2, a Saturday-and-Sunday weekend, and holidays in D2:D10, use:
=NETWORKDAYS.INTL(A2,B2,1,D2:D10)
The number 1 means Saturday and Sunday are weekend days, matching NETWORKDAYS’ default. For a Sunday-and-Monday weekend, use 2 in the weekend position. Excel also accepts a seven-character text pattern that runs from Monday to Sunday: 1 marks a nonworking day and 0 marks a working day. For example, with dates in A2 and B2 and a Saturday-and-Sunday weekend, this formula returns the workday count:
=NETWORKDAYS.INTL(A2,B2,"0000011")
The six zeros mark Monday through Saturday as working days, while the final 1 marks Sunday as a weekend day—so take care: this pattern represents a Sunday-only weekend, not Saturday and Sunday. To mark both Saturday and Sunday as weekends, use "0000011"? No: reading Monday through Sunday, the correct pattern is "0000011" only if the sixth and seventh characters correspond to Saturday and Sunday. In this seven-character pattern, they do: Monday is character one, Saturday is character six, and Sunday is character seven. Thus the pattern marks both Saturday and Sunday as nonworking days.
For a custom schedule, check the seven characters in order from Monday through Sunday. A pattern with one 1 marks one weekly day off; two adjacent 1s can mark a two-day weekend. NETWORKDAYS.INTL also supports numeric weekend codes for common schedules. If you use a numeric code, check the function’s Excel Help for the code that matches your particular weekend arrangement.
Make Sure Excel Recognizes the Dates
Excel stores dates as serial numbers and displays them in a date format. A cell that contains text such as “January 8, 2024” may look like a date but fail to behave like one, depending on how it was entered or imported. If a formula returns #VALUE! or an unexpected result, check that both endpoints and the holiday entries are genuine Excel dates.
Useful checks include:
- Select a date cell and change its number format to General. A recognized date displays as a number rather than remaining as date-like text.
- Check for leading apostrophes, extra spaces, or imported text values.
- Use consistent date entry and regional settings. For instance, 04/05/2024 can be interpreted differently in different date conventions.
- Confirm that the holiday range contains dates, not holiday names such as “Founders Day.”
Formatting a text value as a date does not necessarily convert it into a real date. If data was imported, use Excel’s data-cleaning tools or convert the values before relying on the count. For a quick check, a formula that adds one day to a date cell should produce the following date if Excel recognizes the value as a date.
Inclusive Counts, Reversed Dates, and Edge Cases
A common source of confusion is whether the start date should count. NETWORKDAYS includes it when it is a working day and is not a holiday. If a task starts on Monday and ends on Friday, it returns five, not four. For a measure intended to count only workdays after the starting date, use a formula that deliberately excludes the start date, and decide how your process should handle identical or reversed dates.
When the end date is earlier than the start date, Excel can return a negative count. That can be useful for identifying a reversed interval, but it may not suit a report that expects a positive duration. To count the workdays between the earlier and later of two dates regardless of their order, with the dates in A2 and B2, use:
=NETWORKDAYS(MIN(A2,B2),MAX(A2,B2))
This formula treats the interval as inclusive whichever date is earlier. It does not indicate which date came first, so use a separate validation rule if reversed entries should be flagged rather than normalized. If either date is missing, consider adding a check so the workbook does not report a count for an incomplete record.
Also note that NETWORKDAYS counts the same weekday once in a one-day range. If A2 and B2 both contain a Tuesday that is not a holiday, the result is 1. If that Tuesday is listed as a holiday, the result is 0.
Copy the Formula Down a Table
For a list of projects, keep each start and end date on the same row. With start dates in A2:A100, end dates in B2:B100, and a holiday list in D2:D10, enter this in C2:
=NETWORKDAYS(A2,B2,$D$2:$D$10)
Copy the formula down column C. The date references change to A3 and B3 on the next row, while the dollar signs keep the holiday range anchored. You can fill down by dragging the fill handle or copying and pasting. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. Check a few rows manually, especially where a holiday or weekend falls on an endpoint.
For a schedule with a custom weekend, use NETWORKDAYS.INTL in the same layout. With the custom weekend code or pattern and the holiday list included as arguments, the formula can be copied down in the same way. Keep the pattern and holiday references consistent so every row uses the intended calendar.
NETWORKDAYS Compared with Other Date Formulas
Subtracting one date from another answers a different question. With dates in A2 and B2, =B2-A2 returns the number of elapsed calendar days from the start date to the end date. For January 8 through January 12, 2024, that result is 4, because it measures the distance from Monday to Friday; NETWORKDAYS returns 5 because it counts both working dates. Neither is inherently wrong—the appropriate formula depends on the definition of duration you need.
NETWORKDAYS is for counting working dates within a range. WORKDAY is its related function for finding a date a specified number of workdays before or after another date. For example, a delivery estimate that begins from a given date and skips weekends is a date-finding problem, while counting business days between a start and finish is a NETWORKDAYS problem. Both functions can take holidays into account, but they answer different questions.
For a custom weekend, use NETWORKDAYS.INTL rather than trying to subtract weekend days manually. Manual formulas can become difficult to maintain around partial weeks, holiday dates, and unusual schedules. The built-in function makes the weekend rule explicit and easier for another workbook user to review.
Frequently Asked Questions
Does NETWORKDAYS count the start and end dates?
Yes. It includes either endpoint if that date is a weekday under the selected schedule and is not in the holiday list. A Saturday or Sunday is excluded with the default weekend, even when it is an endpoint.
How do I count workdays and exclude holidays?
Place valid holiday dates in a range and pass that range as the third argument, for example =NETWORKDAYS(A2,B2,D2:D10) when the dates are in A2 and B2 and holidays are in D2:D10.
Why does my workday formula show a date?
The result cell is probably formatted as a date. Change its format to General or Number using Home > Number Format. NETWORKDAYS returns a numeric count, but Excel’s display format can make that number look like a date.
How do I count workdays when weekends are Friday and Saturday?
Use NETWORKDAYS.INTL and set its weekend argument to the pattern or numeric code that marks Friday and Saturday as nonworking. For the seven-character pattern, characters correspond to Monday through Sunday; mark Friday and Saturday with 1 and the other days with 0.
Why is my result negative?
The end date may be earlier than the start date. Decide whether that should be treated as an error or whether you simply want the count for the interval in either order. For an order-independent inclusive count, use =NETWORKDAYS(MIN(A2,B2),MAX(A2,B2)).
Related
- Excel Number Format Color Codes and Custom Formats
- Excel Number Format Brackets for Negative Numbers Explained
- Excel Number Format for Millions: Codes and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Sum a Row in Google Sheets: Simple FormulaTo sum a row in Google Sheets, put the total in an empty cell and enter =SUM(B2:F2). This…
- How to Schedule an Email in Outlook Web App for LaterTo schedule an email in Outlook on the web, compose your message, select the arrow beside Send, choose…
- Split a Google Sheets Column Into Two ColumnsTo split a column in Google Sheets, select the cells, choose Data > Split text to columns, then…
- Excel Not Inserting Columns? Common Causes and FixesIf Excel will not insert a column, first check whether the worksheet is protected, whether cells or formatting…
- Excel Yes or No Drop-Down: Create and Customize ItYes. In Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation > Data…
- Create a Mailing List in Outlook: Steps & TipsTo create a mailing list in Outlook, make a contact list in the People section and add the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.