Categories

# Calculate Working Days and Holidays

If you want to calculate the number of working days between two dates in Excel, you can use the NETWORKDAYS or NETWORKDAYS.INTL functions. Both functions can exclude a list of holidays from the count, but the NETWORKDAYS.INTL function also allows you to specify which days of the week are considered weekends.

1. `NETWORKDAYS` Calculate working days between two dates
This function calculates the number of working days between two dates, excluding weekends and specified holidays.
2. `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.
3. 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 `0`s and `1`s, 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:
• `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.

## 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:

1. 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)``
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"})``
1. 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.

## Date and Time

1. How Date and Time Feature Works in Excel
2. Calculate Date and Time
3. Display Negative Time Values
4. Count All Occurrence of a Specific Day in a Month or Two Dates
5. Calculate Working Days and Holidays
6. Determine Deadlines, Schedules, or Due Dates
7. Show Total Time as Days, Hours, and Minutes
8. Format Date and Time Values