How to Count Holidays in Excel Like a Pro: Easy Formulas for 2025

Hello everyone, I am your dedicated public holiday assistant. Recently, a little friend consulted me about how to count holidays in Excel现在我就把相关问题总结一下，希望能够帮助到想要了解的小伙伴们。

Let’s be real – nobody wants to manually flip through a calendar and tally up holidays when you’ve got a spreadsheet full of deadlines. Whether you’re 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’s the lowdown on the easiest ways to get Excel to count holidays for you automatically.

First up, the superstar function: 网络日. This gem calculates the number of whole workdays between two dates – and it can also exclude a list of holidays you provide. The syntax is =NETWORKDAYS(开始日期，结束日期，[假期]). The “holidays” part is optional, but that’s 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 “Holidays” so your formula stays clean.

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: =NETWORKDAYS(A2, B2, D2:D10). Excel will then return the number of weekdays (Monday through Friday) between those dates, skipping the holidays you listed. Boom – no more manual subtraction.

But what if your workweek isn’t Monday to Friday? No problem. Excel also has 网络日国际 (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 “7” (Friday-Saturday weekend) or even a custom string of 1s and 0s. Same deal – you still pass your holiday range as the third argument.

Now, let’s talk about counting holidays themselves – not just excluding them. Maybe you want a formula that tells you “how many holidays fall in this month” or “between these dates.” That’s a little different, but still simple. Use COUNTIFS 或者 SUMPRODUCT. If your holiday list is in column D, and you have a start date in A1 and end date in B1, you can do: =COUNTIFS(D:D, ">="&A1, D:D, "<="&B1). This counts all holiday dates that land within that range.

Another common need: figuring out the next working day after a holiday. Enter 工作日 （或者 工作日国际). Say today’s date is in A1 and you want the next business day that’s not a holiday. Use =WORKDAY(A1, 1, holidays). If today is a holiday itself, Excel will skip it and jump to the next non-holiday weekday.

Alright, but here’s a pro tip: create a named range for your holiday list. Go to Formulas > Name Manager and give your list a name like “HolidayList”. Then your formulas become super readable: =NETWORKDAYS(start, end, HolidayList). 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 – it’s dynamic.

And don’t 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.

Questions related to how to count holidays in Excel

How do I make Excel automatically update holidays every year? That’s 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’s Day), just drag the date down with the year in a cell. For floating holidays, you’ll need formulas – like for Thanksgiving in the US (fourth Thursday of November), you can use =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. But honestly, most people just maintain a small table.

Can Excel count holidays for multiple countries at once? Yes, but you’ll need separate holiday lists for each country. Then you can use a drop-down to choose which list to reference. Use the INDIRECT function: =NETWORKDAYS(start, end, INDIRECT(holiday_range_name)) where the range name changes based on your selection.

Why is my NETWORKDAYS formula giving a #VALUE error? Usually it’s 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’t text – use DATEVALUE if necessary.

How do I count partial holidays (like half-day off)? Excel can’t 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.

Can I count holidays across multiple sheets? Absolutely. Use a 3D reference like =NETWORKDAYS(start, end, Sheet2!D:D, Sheet3!D:D) (though NETWORKDAYS only accepts one range for holidays – so you’ll need to combine them into one column, or use a helper sheet that pulls all holidays together with VSTACK in newer Excel versions.

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’ve got your holiday list set up, the formulas practically run themselves. And if you ever get stuck, just remember: Excel’s built-in date functions were designed for this exact kind of mess. Play around with them, test on a small dataset, and soon you’ll be the office hero who never misses a holiday count.

I hope this article helps you fully understand how to count holidays in Excel. If you have more questions or run into a specific hiccup, feel free to drop me a line. Happy spreadsheeting!