Excel Formula for Workdays in a Month

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for workdays in a month is =NETWORKDAYS(start_date, end_date). For example, =NETWORKDAYS(DATE(2025,1,1), DATE(2025,1,31)) returns 23 for January 2025, because it excludes Saturdays and Sundays. If your month has holidays, add a range of holiday dates as the third argument.

Why NETWORKDAYS Is the Go-To Function

NETWORKDAYS counts every day between two dates (inclusive) that is not a Saturday or Sunday. It ignores time values, so you can pass full dates or date serial numbers. The function is available in all modern Excel versions, including Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web. It is the standard tool for calculating business days because it is simple and widely supported.

If you need to count workdays starting from a specific day of the week (e.g., a workweek that runs Sunday–Thursday), use NETWORKDAYS.INTL instead. That function lets you specify which days are weekends using a code number or a string of 0s and 1s.

How to Build the Formula for a Specific Month

To get workdays for a month, you need the first and last day of that month. The easiest way is to reference a cell containing any date in the month, then compute the boundaries:

  • First day: =EOMONTH(A1,-1)+1 (where A1 holds any date in the month)
  • Last day: =EOMONTH(A1,0)

So the full formula, with a date in A1 and holidays in a named range Holidays, is:

=NETWORKDAYS(EOMONTH(A1,-1)+1, EOMONTH(A1,0), Holidays)

If you put a real date like 1/15/2025 in A1, the result for January 2025 is 23 (without holidays). If you add a holiday like New Year’s Day (Jan 1) in the Holidays range, it becomes 22.

Using SEQUENCE for a Dynamic Month List

For Excel 365 or Excel 2021, you can generate a list of all workdays in a month with the SEQUENCE function. This is useful if you want to display each workday in separate cells or count them with other criteria.

Assuming A1 contains a date in the month, this formula spills a list of all workdays:

=LET(day, EOMONTH(A1,-1)+1, last, EOMONTH(A1,0), all, SEQUENCE(last-day+1, 1, day), FILTER(all, NETWORKDAYS.INTL(all, all, 1) = 1))

This creates a sequence of every day in the month, then filters to only weekdays. To exclude holidays, you would need to add a condition checking against your holiday range, which requires a more advanced LET formula.

Handling Holidays and Custom Weekends

To exclude holidays, list them in a range (e.g., H2:H10) and pass that range as the third argument to NETWORKDAYS. The holidays can be dates or date serial numbers. For example:

=NETWORKDAYS(DATE(2025,1,1), DATE(2025,1,31), H2:H10)

If your workweek is not Monday–Friday, use NETWORKDAYS.INTL. The weekend code is a number: 1 means Saturday/Sunday, 2 means Sunday/Monday, 7 means Friday/Saturday, and so on. You can also use a 7-character string of 1s and 0s, where 1 means weekend. For example, to make Friday and Saturday the weekend, use code 7 or the string "0000110" (where positions start with Monday).

Common Mistakes and How to Fix Them

  • Using NETWORKDAYS with a single date: The function needs two dates. If you only have a month name, you must first compute the first and last day.
  • Counting the end date incorrectly: NETWORKDAYS includes both the start and end date. If you want to exclude the end date, subtract 1 from the result.
  • Holiday range includes blank cells: If your holiday range has empty cells, NETWORKDAYS ignores them, but it may slow down if the range is huge. Use a defined range like H2:H10 instead of full column references.
  • Using DATEVALUE with text dates: If your month is stored as text like “January 2025”, convert it to a real date first, e.g., =DATEVALUE("1 " & A1).
  • Forgetting to update for leap years: February has 29 days in leap years, but NETWORKDAYS handles that automatically because it uses the actual dates.

Comparing NETWORKDAYS with Other Functions

There is no dedicated “workdays in month” function, but you have alternatives:

  • NETWORKDAYS: Counts weekdays between two dates, excluding weekends and optional holidays. Best for simple counts.
  • NETWORKDAYS.INTL: Same as above but lets you define custom weekends. Useful for non-standard workweeks.
  • WORKDAY: Returns a date that is a given number of workdays before or after a start date. Not for counting, but can be used to find the last workday of a month.
  • WORKDAY.INTL: Custom-weekend version of WORKDAY.
  • DAYS: Simply subtracts two dates, giving total days including weekends. Not a workday function.

For a quick count without holidays, you could also use a formula like =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)),2)<6)), but that is more complex and less efficient than NETWORKDAYS.

Frequently Asked Questions

How do I count workdays in the current month?

Use =NETWORKDAYS(EOMONTH(TODAY(),-1)+1, EOMONTH(TODAY(),0)). This automatically updates as the month changes.

Can I count workdays excluding holidays that fall on weekends?

Yes, just include all holidays in the holiday range. NETWORKDAYS only subtracts holidays that fall on weekdays; if a holiday is on a weekend, it is already excluded by the weekend logic, so it won't double-count.

What if my month has a different number of workdays because of a holiday?

Simply add the holiday dates to the third argument. For example, if July 4th is a holiday, include that date in your holiday range and the count will drop by one.

How do I get the last workday of the month?

Use =WORKDAY(EOMONTH(A1,0)+1, -1) where A1 is a date in the month. This finds the first day of the next month, then goes back one workday.

Does NETWORKDAYS consider time of day?

No, it only looks at the date portion. If you have datetime values, they are truncated to dates internally.

Why does my formula return a #VALUE! error?

This usually happens if the start date is after the end date, or if the holiday range contains text that cannot be interpreted as a date. Check that your dates are real Excel dates, not text.

Tips for Using Workday Formulas in Real Projects

When building a project schedule, combine NETWORKDAYS with conditional formatting to highlight non-workdays. For example, select a range of dates and use a formula rule like =NETWORKDAYS(A1,A1)=0 to shade weekends. For payroll or attendance, store holiday dates in a dedicated sheet and reference that range with a defined name to keep formulas clean.

If you need to count workdays for multiple months at once, you can use a single formula with SEQUENCE to generate a list of month start dates, then apply NETWORKDAYS to each. For example, with month start dates in A2:A13, use =NETWORKDAYS(A2, EOMONTH(A2,0), Holidays) and drag down.

Remember that Excel's date system starts on January 1, 1900, and dates are stored as serial numbers. This is why you can subtract dates directly. Always format your result cells as numbers, not dates, to see the count correctly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.