To count weekdays between two dates in Excel, use =NETWORKDAYS(B2,B3); for example, with a Monday start date in B2 and Friday end date in B3, it returns 5. To calculate a date a set number of working days before or after another date, use WORKDAY. Both functions can also exclude a list of holidays.
Which Excel formula counts business days?
NETWORKDAYS counts working days from a start date through an end date, including both dates when they fall on working days. By default, it treats Saturday and Sunday as weekends. Put real Excel dates in the start and end cells; for example, January 6 and January 10, 2025, are Monday and Friday, so the count is five.
With the start date January 6, 2025, in B2 and the end date January 10, 2025, in B3, the formula is:
=NETWORKDAYS(B2,B3)
The result is 5: Monday, Tuesday, Wednesday, Thursday, and Friday. The function counts the endpoints, so a single weekday used as both the start and end date counts as one working day. If either endpoint is a Saturday or Sunday, that date is not counted.
This is useful for service-level reporting, project schedules, processing times, and staffing summaries. It answers “how many working days are in this date span?” It does not return a due date; use WORKDAY when the question is “what date is a certain number of workdays later?”
How NETWORKDAYS handles holidays
To exclude public holidays, company closures, or other nonworking dates, supply a range of holiday dates as the optional third argument. The holiday cells should contain actual Excel dates, not text that merely looks like a date. Keep the dates in a separate column so the range is easy to review and update.
Suppose B2 contains January 6, 2025, B3 contains January 10, 2025, and D2:D3 contain January 8 and January 9, 2025. With those dates, the formula is:
=NETWORKDAYS(B2,B3,D2:D3)
The result is 3, because Wednesday and Thursday are excluded as holidays; Monday, Tuesday, and Friday remain working days. A holiday on a Saturday or Sunday does not reduce the count a second time, because weekend dates were already excluded.
You can use a longer range, such as D2:D20, even if some cells are blank. In practice, a dedicated holiday list is clearer than typing dates into the formula: it keeps the calculation readable and lets someone check which dates the workbook treats as closures. If different teams or locations observe different holidays, use the appropriate holiday list for each calculation rather than assuming one calendar fits every group.
How WORKDAY returns a due date
WORKDAY moves forward or backward by a specified number of working days, skipping Saturday and Sunday by default. The starting date is not counted as day one when the number of days is positive. This distinction matters when setting a response deadline: five workdays after Monday is the following Monday, not Friday.
With a start date of January 6, 2025, in B2, and the number of working days, 5, in C2, use:
=WORKDAY(B2,C2)
The result is January 13, 2025. Tuesday, January 7, is day one, and Monday, January 13, is day five. If C2 contains 0, the result is the start date itself, even if that date falls on a weekend.
To account for holidays as well, suppose D2:D3 contain January 8 and January 9, 2025. With the start date in B2, the day count in C2, and holidays in D2:D3, use:
=WORKDAY(B2,C2,D2:D3)
For a start date of January 6 and a count of 5, the result is January 15, 2025. The five working days after the start are January 7, 10, 13, 14, and 15. The holiday dates are skipped along with the weekend.
Custom weekend patterns with the INTL functions
Some organizations do not use Saturday and Sunday as their weekend. The NETWORKDAYS.INTL and WORKDAY.INTL functions let you specify a different weekend pattern. Their weekend argument can be a numeric code or a seven-character text string. In the string, Monday is the first character and Sunday is the seventh; 1 means nonworking and 0 means working.
For example, the string "0000110" marks Friday and Saturday as nonworking days. With January 6, 2025, in B2 and January 12, 2025, in B3, this formula counts the working days in the range using a Friday–Saturday weekend:
=NETWORKDAYS.INTL(B2,B3,"0000110")
The result is 5: Monday through Thursday and Sunday count; Friday and Saturday do not. To exclude holidays as well, add a fourth argument containing the holiday range. Make sure the seven positions are in Monday-to-Sunday order, since reversing the order creates a different schedule.
For the corresponding due-date calculation, with a start date in B2, a workday count in C2, and the same Friday–Saturday weekend, use:
=WORKDAY.INTL(B2,C2,"0000110")
With B2 set to January 6, 2025, and C2 set to 5, the result is January 13, 2025. Tuesday, Wednesday, Thursday, Sunday, and the following Monday are the five working days. Add a fourth argument for holidays when your calendar requires it.
Counting backward and handling deadlines
Both functions accept negative day counts where applicable. A negative value in WORKDAY moves backward, which can help calculate a preparation date before a deadline. The count still skips weekend days, and an optional holiday range is skipped too.
With January 10, 2025, in B2 and -3 in C2, use:
=WORKDAY(B2,C2)
The result is January 7, 2025: January 9 is one working day back, January 8 is two, and January 7 is three. This counts working days before the start date, not the start date as the first day. Confirm the counting convention in a policy or contract before using a spreadsheet date as an official deadline; Excel calculates the dates you specify but does not determine which legal or business rule applies.
For a count of business days between two dates, use NETWORKDAYS rather than subtracting one date from the other. Ordinary date subtraction measures calendar-day intervals and includes weekends. It can therefore disagree with a working-day service target even when both calculations use the same endpoints.
Entering dates and reading the result
Excel stores dates as serial values and displays them according to the cell’s number format. The formula result from WORKDAY is a date value, so if it appears as a number, change the result cell’s format to a date. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1. Choose a date format that makes the day, month, and year unambiguous for your audience.
Be cautious when dates are imported from another system. A value that looks like “1/6/2025” might be interpreted differently depending on regional settings, or it might be stored as text rather than as a date. If a function returns an unexpected result, check that the input cells contain valid date values and that the holiday list uses the same date system. In Microsoft 365 for Windows, the functions described here work in the same way in the desktop app and Excel for the web; menu layouts can differ, but the formulas do not.
Time values are another potential source of confusion. These functions calculate by date, not by hours worked. If a cell contains both a date and a time, the function’s business-day result does not measure elapsed working hours or partial shifts. For hourly schedules, breaks, or rotating shifts, a separate time-based model is needed.
Common mistakes and how to fix them
- Expecting the start date to be day one in WORKDAY. A positive count begins with the next working date. Use zero if you want the starting date returned, or adjust the count only if your intended convention calls for it.
- Forgetting that NETWORKDAYS includes both endpoints. A Monday-to-Friday span is five days, not four. If your business rule excludes an endpoint, be explicit about that rule and adjust the inputs or calculation accordingly.
- Leaving out holidays. The default functions know about weekends, not company closures or public holidays. Add a holiday range whenever those dates should be excluded.
- Using text instead of dates. Text-formatted dates may not be recognized consistently. Convert imported values to real dates and verify the displayed result before relying on it.
- Choosing the wrong weekend pattern. The basic functions always assume Saturday and Sunday. Use an INTL function when the schedule has different weekly nonworking days, and verify the order of a custom seven-character pattern.
- Misreading a numeric output. A date can display as a serial number when the output cell is formatted as General or Number. Apply a date format rather than changing the calculation.
Choosing the right function
Choose the function based on the question you need to answer. NETWORKDAYS counts workdays in a span; WORKDAY returns a date after or before a number of workdays. Add the holiday argument when nonworking dates extend beyond the usual weekend. Choose the INTL version when the weekend days differ from Saturday and Sunday.
- “How many working days are between these two dates?” Use
NETWORKDAYSorNETWORKDAYS.INTL. - “What is the due date after a set number of workdays?” Use
WORKDAYorWORKDAY.INTL. - “How many workdays remain, excluding our closure dates?” Use a counting function and supply the holiday list.
- “What date is a set number of shifts away?” A simple workday formula may not be enough if shifts rotate or depend on hours; model the actual schedule.
These functions are available in current Microsoft Excel, including Microsoft 365. The basic and INTL functions are not newer dynamic-array features, so they are not limited to Microsoft 365. The same formula syntax works in Excel for Mac, although keyboard shortcuts and some interface details differ from Windows.
Frequently Asked Questions
Does NETWORKDAYS count the start and end dates?
Yes. It includes both endpoints if they are working days. It excludes an endpoint if that date is a weekend or appears in the supplied holiday list.
How do I exclude public holidays in Excel?
Enter the holidays as real dates in a worksheet range, then provide that range as the holiday argument in NETWORKDAYS, WORKDAY, or the corresponding INTL function.
What is the difference between NETWORKDAYS and WORKDAY?NETWORKDAYS returns a count of working days between two dates. WORKDAY returns a date that is a specified number of working days before or after a starting date.
Can Excel count business days with a Friday–Saturday weekend?
Yes. Use NETWORKDAYS.INTL to count a date range or WORKDAY.INTL to calculate a date, and provide a weekend code or seven-character pattern for the schedule.
Why does WORKDAY show a number instead of a date?
The result cell is likely formatted as General or Number. Change its number format to a date; the underlying result is still an Excel date value.
Does Excel business-day math account for working hours?
No. These functions count whole working dates and skip configured nonworking days. They do not calculate hours, shifts, lunch breaks, or partial working days.
Related
- Excel Data Validation: Can You Select Multiple Items?
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Box Plot Formula: Quartiles, IQR, and WhiskersA box plot has no single formula: it is built from a five-number summary—minimum, first quartile (Q1), median,…
- Excel MID Function: Extract Text from a Cell’s MiddleTo extract characters from the middle of a cell in Excel, use the MID function, which takes a…
- Excel AGGREGATE Formula: Two Practical Examples ExplainedUse Excel’s AGGREGATE function to calculate a result while choosing whether to ignore errors, hidden rows, or both.…
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- How to Delete an Array Formula in Excel Without ErrorsTo delete an array in Excel, remove its formula: select the entire output range for a legacy array…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.