![\"How](\"https:\/\/www.publicholidaycalendar.com\/wp-content\/uploads\/2026\/05\/pexels-photo-806408-1.jpeg\")
<\/p>\n<\/p>\n
Hello everyone, I am your dedicated public holiday assistant. Recently, a little friend consulted me about how to count holidays in Excel<\/b>. Now I will summarize the relevant problems, hoping to help the little friends who want to know.<\/p>\n
Let\u2019s be real \u2013 nobody wants to manually flip through a calendar and tally up holidays when you\u2019ve got a spreadsheet full of deadlines. Whether you\u2019re planning a project timeline, calculating PTO accrual, or just trying to figure out how many actual workdays are left in the month, Excel can do the heavy lifting for you. And the best part? Once you set it up, you never have to count holidays again. Here\u2019s the lowdown on the easiest ways to get Excel to count holidays for you automatically.<\/p>\n
First up, the superstar function: NETWORKDAYS<\/b>. This gem calculates the number of whole workdays between two dates \u2013 and it can also exclude a list of holidays you provide. The syntax is For example, say you have a start date in cell A2 and end date in B2, and your holiday dates are listed in D2 through D10. Your formula would be: But what if your workweek isn\u2019t Monday to Friday? No problem. Excel also has NETWORKDAYS.INTL<\/b> (available in Excel 2010 and later). This lets you pick which days are weekends. For instance, if your company works Sunday through Thursday, you can use a weekend code like \u201c7\u201d (Friday-Saturday weekend) or even a custom string of 1s and 0s. Same deal \u2013 you still pass your holiday range as the third argument.<\/p>\n Now, let\u2019s talk about counting holidays themselves \u2013 not just excluding them. Maybe you want a formula that tells you \u201chow many holidays fall in this month\u201d or \u201cbetween these dates.\u201d That\u2019s a little different, but still simple. Use COUNTIFS<\/b> or SUMPRODUCT<\/b>. If your holiday list is in column D, and you have a start date in A1 and end date in B1, you can do: Another common need: figuring out the next working day after a holiday. Enter WORKDAY<\/b> (or WORKDAY.INTL<\/b>). Say today\u2019s date is in A1 and you want the next business day that\u2019s not a holiday. Use Alright, but here\u2019s a pro tip: create a named range for your holiday list. Go to Formulas > Name Manager and give your list a name like \u201cHolidayList\u201d. Then your formulas become super readable: And don\u2019t forget about conditional formatting. You can highlight cells that contain holiday dates using a rule based on the COUNTIF formula. That way, when you look at your calendar, holidays pop out visually.<\/p>\n How do I make Excel automatically update holidays every year?<\/b> That\u2019s a bit trickier since holidays change dates (like Thanksgiving or Easter). You can manually update your list each year, or use a lookup table that calculates holidays based on year references. For fixed-date holidays (like New Year\u2019s Day), just drag the date down with the year in a cell. For floating holidays, you\u2019ll need formulas \u2013 like for Thanksgiving in the US (fourth Thursday of November), you can use Can Excel count holidays for multiple countries at once?<\/b> Yes, but you\u2019ll need separate holiday lists for each country. Then you can use a drop-down to choose which list to reference. Use the INDIRECT function: Why is my NETWORKDAYS formula giving a #VALUE error?<\/b> Usually it\u2019s because the holiday list contains non-date values or blank cells. Make sure all entries in your holiday range are actual dates. Also, check that your start and end dates aren\u2019t text \u2013 use DATEVALUE if necessary.<\/p>\n How do I count partial holidays (like half-day off)?<\/b> Excel can\u2019t handle half-days natively. A workaround is to create a column beside your holiday list that indicates the fraction (0.5 for half-day). Then use a SUMPRODUCT formula that multiplies the count by the fraction.<\/p>\n Can I count holidays across multiple sheets?<\/b> Absolutely. Use a 3D reference like Counting holidays in Excel is one of those skills that saves you hours down the road. Whether you use NETWORKDAYS for excluding them or COUNTIFS for tallying them up, once you\u2019ve got your holiday list set up, the formulas practically run themselves. And if you ever get stuck, just remember: Excel\u2019s built-in date functions were designed for this exact kind of mess. Play around with them, test on a small dataset, and soon you\u2019ll be the office hero who never misses a holiday count.<\/p>\n I hope this article helps you fully understand how to count holidays in Excel<\/b>. If you have more questions or run into a specific hiccup, feel free to drop me a line. Happy spreadsheeting!<\/p>\n","protected":false},"excerpt":{"rendered":" Hello everyone, I am your dedicated public holiday assistant. Recently, a little friend consulted me about how to count holidays in Excel. Now I will summarize the relevant problems, hoping to help the little friends who want to know. Let\u2019s be real \u2013 nobody wants to manually flip through a calendar and tally up holidays…<\/p>","protected":false},"author":1,"featured_media":25797,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"slim_seo":{"title":"How to Count Holidays in Excel Like a Pro: Easy Formulas for 2025 - Public Holiday Calendar","description":"Hello everyone, I am your dedicated public holiday assistant. Recently, a little friend consulted me about how to count holidays in Excel . Now I will summarize"},"footnotes":""},"categories":[278],"tags":[26375,26377,15824,26376,3784],"class_list":["post-25798","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-excel-count-holidays-formula","tag-excel-working-days-calculator","tag-exclude-holidays-excel","tag-holiday-list-excel","tag-networkdays-function"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/posts\/25798","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/comments?post=25798"}],"version-history":[{"count":1,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/posts\/25798\/revisions"}],"predecessor-version":[{"id":25799,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/posts\/25798\/revisions\/25799"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/media\/25797"}],"wp:attachment":[{"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/media?parent=25798"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/categories?post=25798"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.publicholidaycalendar.com\/de\/wp-json\/wp\/v2\/tags?post=25798"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}=NETWORKDAYS(start_date, end_date, [holidays])<\/code>. The \u201cholidays\u201d part is optional, but that\u2019s where you put your list of holiday dates. Just make sure those dates are in an Excel date format (like 1\/1\/2025). You can even put them in a separate column and name that range \u201cHolidays\u201d so your formula stays clean.<\/p>\n=NETWORKDAYS(A2, B2, D2:D10)<\/code>. Excel will then return the number of weekdays (Monday through Friday) between those dates, skipping the holidays you listed. Boom \u2013 no more manual subtraction.<\/p>\n=COUNTIFS(D:D, \">=\"&A1, D:D, \"<=\"&B1)<\/code>. This counts all holiday dates that land within that range.<\/p>\n=WORKDAY(A1, 1, holidays)<\/code>. If today is a holiday itself, Excel will skip it and jump to the next non-holiday weekday.<\/p>\n=NETWORKDAYS(start, end, HolidayList)<\/code>. You can also use an Excel Table so the list automatically expands when you add new holidays. Just insert a table for your holiday data and reference the column \u2013 it\u2019s dynamic.<\/p>\nQuestions related to how to count holidays in Excel<\/h2>\n
=DATE(year,11,1)+IF(WEEKDAY(DATE(year,11,1))>5,7-WEEKDAY(DATE(year,11,1))+5,5-WEEKDAY(DATE(year,11,1)))+21<\/code>. But honestly, most people just maintain a small table.<\/p>\n=NETWORKDAYS(start, end, INDIRECT(holiday_range_name))<\/code> where the range name changes based on your selection.<\/p>\n=NETWORKDAYS(start, end, Sheet2!D:D, Sheet3!D:D)<\/code> (though NETWORKDAYS only accepts one range for holidays \u2013 so you\u2019ll need to combine them into one column, or use a helper sheet that pulls all holidays together with VSTACK in newer Excel versions.<\/p>\n