Calculating Holidays: A Handy Excel Guide. In this article, we’ll explore how to count holidays between dates in Excel.
Calculating Holidays: A Handy Excel Guide
Scenario: Calculating Holidays
When dealing with date values, determining the count of non-working days in Excel, including national holidays and weekends, can be essential. To achieve this, we’ll break down the problem into two steps, outlined below, along with the corresponding formula syntax.
How to Solve the Problem: Calculating Holidays
In this tutorial, we’ll utilize the DATEDIF function and NETWORKDAYS function. We’ll create a formula using these functions, considering two specific dates and a list of national holidays to calculate the count of holidays or non-working days.
Formula Syntax: Calculating Holidays
=DATEDIF(start_date, end_date, "d") - NETWORKDAYS(start_date, end_date, [holidays])
- start_date: Starting point from the date to begin counting.
- end_date: Endpoint until the date to count.
- “d”: Counts the number of days, disregarding month and year values.
- [holidays]: [Optional] Reference to the cell containing the list of holidays.
Example: Calculating Holidays
Let’s understand the formula by applying it to a specific example. Consider the following formula:
=DATEDIF(C4, D4, "d") - NETWORKDAYS(C4, D4, G3:G11)
Explanation:
- DATEDIF function calculates the total days between the given dates, considering only day values.
- NETWORKDAYS function computes the total working days, including weekends (Saturdays and Sundays) and holidays provided as an array reference.
- The difference between total days and working days gives the count of non-working days or holidays in Excel using this formula.
In our example, there are 13 non-working days between January 1st and February 9th. The total days between these dates are 39, out of which 26 are working days. The formula result is 13. You can replicate this formula for other date ranges by copying it and adjusting the holiday array reference.
Observational Notes:
- The formula works exclusively with date values.
- The holidays argument is optional, and you can customize the values for holidays, excluding weekends (Saturdays and Sundays).
- Use the DATE function if an invalid date format is encountered.
- Ensure the start date is before the end date; otherwise, the function will return a #NUM! error.
We hope this tutorial on counting holidays between dates in Excel has been helpful. For more articles on COUNTIF functions and related topics, feel free to explore our other resources.
