Excel Count Days Between Dates Excluding Weekends

To count the number of days between two dates in Excel while excluding weekends (Saturday and Sunday), use the NETWORKDAYS function. You enter the start date, end date, and optionally a range of holiday dates, and Excel returns the count of working days—no manual filtering required.

Formula

The standard Excel formula is:

=NETWORKDAYS(start_date, end_date, [holidays])

• start_date – the earlier date (as a date value, cell reference, or DATE() function).
• end_date – the later date.
• [holidays] – optional; a range of dates to exclude as non-working days.

Excel counts the start date and end date as part of the result. If your start date is a Saturday, it is not counted. If the end date is a Sunday, it is not counted.

Worked Example

Using the defaults from the calculator: start date = 2024-01-01 (a Monday), end date = 2024-01-31 (a Wednesday).

1. Total calendar days between Jan 1 and Jan 31 inclusive = 31 days.
2. Weekends in that period: 8 days (Saturdays: 6, 13, 20, 27; Sundays: 7, 14, 21, 28).
3. Working days = 31 – 8 = 23.

So =NETWORKDAYS("2024-01-01", "2024-01-31") returns 23. The calculator above shows the same result.

When to Use NETWORKDAYS vs. Other Functions

Excel offers several date functions. Choose based on what you need to exclude:

• NETWORKDAYS – excludes Saturdays and Sundays. Best for most business-day counts (e.g., project timelines, invoice aging).
• NETWORKDAYS.INTL – lets you specify which days are weekends (e.g., Sunday only, or Friday-Saturday). Use when your workweek is not Monday–Friday.
• DAYS or simple subtraction (=end-start) – counts all calendar days including weekends. Use when weekends don’t matter.
• WORKDAY – returns a date that is a given number of working days before/after a start date. Useful for adding “5 business daysâ€� to a date.

How to Include Holidays

To exclude holidays, add a third argument to NETWORKDAYS. For example, if you have a list of holiday dates in cells A2:A10, use:

=NETWORKDAYS(B1, B2, A2:A10)

Holidays must be actual date values, not text. If you type “Jan 1, 2024� as text, Excel may not recognize it. Use the DATE() function or a cell formatted as a date.

Common Mistakes and Pitfalls

• Reversed dates – If start_date is later than end_date, NETWORKDAYS returns a negative number. Check your cell references.
• Date stored as text – If your “datesâ€� are text strings, Excel may treat them as 0. Use DATEVALUE() or format cells as dates.
• Including the end date – NETWORKDAYS is inclusive of both start and end. If you need to exclude the end date, subtract 1 from the result.
• Weekend definition – NETWORKDAYS assumes Saturday/Sunday. For other weekends, use NETWORKDAYS.INTL.

Limitations and Edge Cases

NETWORKDAYS works with Excel’s date system, which starts on January 1, 1900 (for Windows) or January 1, 1904 (for Mac). If you work with dates before 1900, the function may not work correctly. Also, the function only excludes weekends and holidays—it does not account for half-days, shifts, or non-standard work hours.

For very large date ranges (e.g., spanning decades), the function handles them fine, but ensure your Excel version supports the date range. Excel can handle dates up to 9999.

Frequently Asked Questions

How do I count days excluding weekends but including holidays?

Use NETWORKDAYS with the holidays argument. If you don’t have a holiday list, you can use an array constant like =NETWORKDAYS(A1, B1, {"2024-01-01","2024-12-25"}) but this is less flexible than a cell range.

Can I exclude only Sundays (not Saturdays)?

Yes, use NETWORKDAYS.INTL. For example, =NETWORKDAYS.INTL(start, end, 11) treats Sunday as the only weekend day. The “weekendâ€� argument is a number or string that defines which days are off.

Why does NETWORKDAYS return a negative number?

This happens when the start date is later than the end date. Swap the dates or use ABS() if you need a positive count regardless of order.

Does NETWORKDAYS count the start and end dates?

Yes, both are included. If you want to exclude the end date (e.g., count days up to but not including), subtract 1 from the result.

How do I count business days in Excel without NETWORKDAYS?

You can use a formula like =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)),2)<6)) but this is complex and less efficient. NETWORKDAYS is the standard, built-in solution.

What if my dates are in a table with many rows?

Use NETWORKDAYS in a new column next to your data. For example, if start dates are in A2:A100 and end dates in B2:B100, enter =NETWORKDAYS(A2, B2) in C2 and drag down.

Related

• Realme 16 Pro Plus vs Vivo V70 Elite: 5G Budget Pick
• How AI Wearables Collect, Process & Store Your Data
• Disable Notifications Lock Screen: Android & iOS

in start | Start date | - | 2024-01-01 | 0
in end | End date | - | 2024-01-31 | 0
out workdays | Working days | days | NETWORKDAYS(start, end) | 0