Calculate Working Days and Holidays in Excel

NETWORKDAYS Calculate working days between two dates
This function calculates the number of working days between two dates, excluding weekends and specified holidays.

NETWORKDAYS.INTL Calculate working days by custom weekend days
This function performs the same role as NETWORKDAYS but with an additional argument allowing weekend customization.
Count the number of holidays and weekends between two dates

The NETWORKDAYS Function

The NETWORKDAYS function returns the number of whole working days (business days) between a start date and an end date, excluding weekends (Saturday and Sunday). You can optionally specify a range of cells that contain the dates of holidays, which are also excluded from the calculation. The syntax of the NETWORKDAYS function is:

=NETWORKDAYS(start_date, end_date, [holidays])

start_date and end_date are the dates that define the period you want to calculate. They can be entered as text, serial numbers, or cell references.
holidays is an optional argument that specifies a range of cells that contain the dates of holidays or non-working days. These dates are excluded from the calculation.

For example, if you want to calculate the number of working days between 1/Dec/2023 and 31/Dec/2023, excluding weekends, you can use this formula:

=NETWORKDAYS("1/Dec/2023", "31/Dec/2023")
"OR use a different date format"
=NETWORKDAYS("2023-12-01", "2023-12-31")

The result is 21.

You can also optionally specify a range of cells (or a date) that contains the dates of holidays, which are also skipped in the calculation. For example, to exclude weekends and the Christmas holiday on 25/Dec/2023, you can use this formula:

=NETWORKDAYS("1/Dec/2023", "31/Dec/2023", "25/Dec/2023")
OR use a different date format
=NETWORKDAYS("2023-12-01", "2023-12-31", "2023-12-25")

The result is 20.

If you want to exclude multiple public holidays, you can use the following formula:

=NETWORKDAYS(A2,B2,C2:C6)
OR
=NETWORKDAYS("2023-12-01", "2023-12-31", C2:C6)

where A2 is the start date, B2 is the end date, and C2:C6 is a range that contains the holiday dates. The result will be 16, which means there are 16 working days in December, excluding the weekends and holidays, see figure.

The NETWORKDAYS.INTL Function

The weekend days vary across different countries. While most countries follow a Saturday–Sunday weekend, some countries have a Friday-Saturday or a Thursday-Friday weekend. The NETWORKDAYS function is designed for the Saturday–Sunday weekend only. If you are working with a different weekend schedule, you need to use the NETWORKDAYS.INTL function instead of the NETWORKDAYS function.

The NETWORKDAYS.INTL function is similar to the NETWORKDAYS function, but it gives you more flexibility to customize your weekends. The syntax of the function is:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

start_date and end_date are the dates that define the period you want to calculate. They can be entered as text, serial numbers, or cell references.

weekend is an optional argument that specifies which days of the week are considered as weekends. It can be entered as a number from 1 to 7 or 11 to 17. It also can be entered as a string of seven 0s and 1s, where 1 represents a non-working day and 0 represents a working day. The default value is 1, which means Saturday and Sunday are weekends. The table below displays the numerical and string representations of the weekend days, as follows:

Number	String	Weekend Days
1	“0000011”	Saturday, Sunday
2	“1000001”	Sunday, Monday
3	“1100000”	Monday, Tuesday
4	“0110000”	Tuesday, Wednesday
5	“0011000”	Wednesday, Thursday
6	“0001100”	Thursday, Friday
7	“0000110”	Friday, Saturday
11	“0000001”	Sunday
12	“1000000”	Monday
13	“0100000”	Tuesday
14	“0010000”	Wednesday
15	“0001000”	Thursday
16	“0000100”	Friday
17	“0000010”	Saturday

holidays is an optional argument that specifies a range of cells that contain the dates of holidays or non-working days. These dates are excluded from the calculation.

For example, to calculate the number of working days between August 1, 2023 and August 31, 2023, excluding Thursday and Friday (as weekend days) as well as August 23 as a holiday.

You can use either 6 or "0001100" in the weekend argument to consider Thursday and Friday as weekend days. See the following formula:

=NETWORKDAYS.INTL("8/1/2023","8/31/2023", 6, "8/23/2023")
"OR"
=NETWORKDAYS.INTL("8/1/2023","8/31/2023", "0001100", "8/23/2023")

This formula returns 21, which is the number of working days in August 2023.

Here are more examples of how to use the NETWORKDAYS.INTL function with different weekend arguments:

Assuming B1 is 1-Aug-2023 and B2 is 31-Aug-2023:

=NETWORKDAYS.INTL(B1,B2)
Returns 23 working days, it uses the default weekend argument of 1, which means Saturday and Sunday are weekends.

=NETWORKDAYS.INTL(B1,B2, 1, C2:C3)
Returns 21 working days, it uses the weekend argument of 1, which means Saturday and Sunday are weekends. It also uses the holiday argument to exclude the holidays in specified in the C2:C3 cell range.

=NETWORKDAYS.INTL(B1,B2,6)
Returns 22 working days, it uses the weekend argument of 6, which means Thursday and Friday are weekends.

=NETWORKDAYS.INTL(B1,B2,5)
Returns 21 working days, it uses the weekend argument of 5, which means Wednesday and Thursday are weekends.

=NETWORKDAYS.INTL(B1,B2,"0000111")
Returns 19 working days, it uses the weekend argument of "0000111", which means Friday, Saturday and Sunday are weekends.

=NETWORKDAYS.INTL(B1,B2,"1111111")
Returns 0 working days, the weekend argument of "1111111" means every day is a weekend.

=NETWORKDAYS.INTL(B1,B2,"0000000")
Returns 31 working days, the weekend argument of "0000000" means no weekend days.

Read Use NETWORKDAYS.INTL to Determine the Number of Specified Days in Any Month.

Count the Number of Holidays & Weekends Between Two Dates

=end_date-start_date+1 - NETWORKDAYS(start_date,end_date,[holidays])

If you want to calculate the number of days off between two dates, you can use a simple formula that subtracts the number of working days from the total number of days. Here is how to calculate the total off days of August 2023:

Calculate the number of days between August 1, 2023 and August 31, 2023, the following formula will return 31:

=("8/31/23"-"8/1/23"+1)

Calculate the working days between August 1, 2023 and August 31, 2023 excluding holidays on August 14 and August 15. The following formula will return 21:

=NETWORKDAYS("8/1/23","8/31/23",{"8/14/23","8/15/23"})

Subtract the working days from the total days. The following formula will return 10:

=("8/31/23"-"8/1/23"+1) - NETWORKDAYS("8/1/23","8/31/23",{"8/14/23","8/15/23"})

This formula will return 10 the number of non-working days between the two dates, including weekends and holidays.

Note: If you see 10-Jan-00 instead of 10, format the cell as General . Press CTRL+1 and select Number tab from the Format Cells dialog box, choose General, and click OK to close the dialog box.

The NETWORKDAYS Function

The NETWORKDAYS.INTL Function

Count the Number of Holidays & Weekends Between Two Dates

Date and Time