Counting holidays between dates in Excel
Counting holidays between dates in Excel. In this tutorial, we will learn how to count holidays between two dates in Excel.
Counting holidays between dates in Excel
Scenario: When dealing with date values, there may be a need to calculate the number of non-working days in Excel, which includes national holidays and weekends. We will break down the solution into two steps using specific formulas.
How to solve the problem? To achieve this, we will use the DATEDIF function and NETWORKDAYS function. Let’s create a formula using these functions. The formula will take two dates as input, along with a list of national holidays, and return the count of holidays or non-working days.
Formula Syntax: =DATEDIF(start_date, end_date, “d”) – NETWORKDAYS(start_date, end_date, [holidays])
Counting holidays between dates in Excel
Explanation of formula arguments:
- start_date: The initial date to start counting from.
- end_date: The final date to count up to.
- “d”: This argument counts the number of days, ignoring the month and year values.
- [holidays]: (Optional) A reference to a range of cells containing holiday dates.
Counting holidays between dates in Excel
Example: Let’s test the formula with an example. We will calculate the count of holidays between two date values, considering the given holidays.
Use the formula: =DATEDIF(C4, D4, “d”) – NETWORKDAYS(C4, D4, G3:G11)
Explanation: The DATEDIF function calculates the total number of days between the two given dates, using “d” to count only the dates, ignoring months and years.
The NETWORKDAYS function calculates the total number of working days between the two dates, considering weekends (Saturday and Sunday) and holidays provided as an array reference.
The difference between the total days and working days gives us the count of non-working days or holidays in Excel using the formula.
In the example, the formula calculates 13 non-working days between January 1st and February 9th. The total days between the dates are 39, and there are 26 working days. The difference is 13 days. You can copy the formula to other cells by freezing the holidays array reference (G3:G11) and using Ctrl + D.
Notes:
- The formula works only with date values.
- The [holidays] argument is optional and allows customizing holidays (excluding weekends) based on the provided array reference.
- Use the DATE function to handle invalid date formats.
- Ensure that the start date comes before the end date; otherwise, the function will return a #NUM! error.
By following this tutorial, you can efficiently count holidays or non-working days between dates in Excel. For more tutorials on COUNTIF functions and other Excel features, check out our other articles.
