Count holidays between dates in Excel

In this article, we will learn how the count holidays between dates in Excel

Scenario:

While working with date values, sometimes we need to get the count of non working days in excel having national holidays and weekends. For this we will breakdown the problem in two steps mentioned below with formula syntax.

How to solve the problem?

For this article we will be needing to use the DATEDIF function & NETWORKDAYS function. Now we will make a formula out of the mentioned function. Here we will given two dates and list of national holidays and count of holidays or non working days to extract.
Formula Syntax:

start_date : start, count from the date.

end_date : end, count to the date.

"d" : count the number of days ignoring month and year value.

[holidays] : [optional] holidays given as cell reference.

Example:

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we will perform the formula over date values with given holidays.

Use the formula:

Explanation:

1. DATEDIF function returns the number of total days between the two given dates. "D" argument used to get the count of dates only ignoring month and year value.
2. NETWORKDAYS function returns the total number of working days between given two dates including Saturday and Sunday (by default) and holidays given as array reference.
3. The difference between total days and working days returns the non working days or holidays in excel using the formula.

Here the array to the function is given as array references as mentioned in the different color cells. Check the cells before using the date cells as arguments. Press Enter to get the result. =39-26

As you can see 13 non working days are there between 1st Jan & 9th Feb. Total days between the dates are 39 and 26 are working days. The difference is 13 days. Now copy the formula to other cells using the Ctrl + D after freezing the holidays array reference.

Here we have the holidays count we require.

Here are all the observational notes regarding using the formula.

Notes:

1. The formula only works with with date values only
2. Holidays argument is optional and values in holidays can be customized except Sat & Sun (weekends)
3. Use the DATE function in place for the invalid date format is used.
4. Start date must be before of the end date. Or else the function returns #NUM! error.

Hope this article about how to Count holidays between days in Excel is explanatory. Find more articles on COUNTIF functions here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Related Articles

COUNTIFS with Dynamic Criteria Range : Count cells depstartent on other cell values in Excel.

COUNTIFS Two Criteria Match : Count cells matching two different criteria on list in excel.

COUNTIFS With OR For Multiple Criteria : Count cells having multiple criteria match using the OR function.

The COUNTIFS Function in Excel : Count cells dependent on other cell values.

How to Use Countif in VBA in Microsoft Excel : Count cells using Visual Basic for Applications code.

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel.

Popular Articles

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.