Calculating Business Days in Excel

To calculate the total number of business days (working days) in Excel using a formula, you can use the EOMONTH and NETWORKDAYS functions. EOMONTH is used to get the last date of the month, while NETWORKDAYS returns the total number of business days in the month between two dates.

In this tutorial, we will show you how to use this formula and how to include holidays in the calculation.

Excel Formula for Total Business Days

Follow these steps to use the formula:

1. In a cell, enter the NETWORKDAYS function.
2. In the first argument (start_date), refer to the cell containing the starting date of the month.
3. In the second argument (end_date), enter the EOMONTH function.
4. In the EOMONTH function, specify the starting date of the month from step 2 and enter 0 for the months argument.
5. Close the EOMONTH function with a closing parenthesis.
6. In the [holidays] argument of the NETWORKDAYS function, specify a list of holidays (valid dates).
7. Close the NETWORKDAYS function with a closing parenthesis and hit enter to get the result.

=NETWORKDAYS(A1,EOMONTH(A1,0),A4:A14)

=NETWORKDAYS(start_date,EOMONTH(start_date,0),list_of_holidays)

How the Formula Works

EOMONTH returns the last date of the month when a date is specified in the function. The last date is used as the end date in the NETWORKDAYS function to calculate the total number of business days between the start and end dates.

NETWORKDAYS considers weekends (Saturdays and Sundays) and holidays in the calculation of business days. By specifying a list of holidays, the function can exclude those days from the calculation.

For example, if we use January 1, 2023, as the start date, we have 31 days in total. There are 10 weekends and holidays in the month, which means 21 working days for a business. When we use the formula, we get the exact number of business days as the result.