Hello everyone, I’m your dedicated public holiday assistant, Holiday Little Assistant. Recently, a little friend reached out to me asking about how to count days in Excel while excluding weekends and holidays. It’s a common question, especially for those managing project timelines or tracking deadlines. So, I’ve put together this guide to help you master this Excel trick. Let’s dive in!

Counting days in Excel is super useful, but when you’re dealing with work schedules or project plans, weekends and holidays can throw off your calculations. Luckily, Excel has built-in functions to make this process a breeze. Whether you’re a beginner or an Excel pro, I’ll walk you through the steps to get it done.

Why Do You Need to Exclude Weekends and Holidays in Excel?

Before we jump into the formulas, let’s talk about why this is important. Imagine you’re planning a project that takes 10 working days to complete. If you simply count 10 days from today, you might end up including weekends or public holidays, which aren’t actual working days. This could mess up your deadlines and create confusion. By excluding weekends and holidays, you get a more accurate timeline that reflects real working days.

How to Count Days Excluding Weekends in Excel

To exclude weekends, Excel has a handy function called NETWORKDAYS. Here’s how it works:
1. Open your Excel sheet and select the cell where you want the result to appear.
2. Type the formula: =NETWORKDAYS(start_date, end_date).
3. Replace start_date and end_date with the actual dates you’re working with.

For example, if your project starts on January 1, 2024, and ends on January 15, 2024, the formula would look like this: =NETWORKDAYS(“1/1/2024”, “1/15/2024”). Excel will automatically exclude Saturdays and Sundays from the count.

How to Exclude Holidays in Excel

Now, let’s take it a step further and exclude holidays too. The NETWORKDAYS function can also handle this. Here’s how:
1. Create a list of holidays in a separate column or range in your Excel sheet.
2. Use the formula: =NETWORKDAYS(start_date, end_date, holidays).
3. Replace holidays with the range of cells containing your holiday dates.

For example, if your holidays are listed in cells A1:A5, the formula would look like this: =NETWORKDAYS(“1/1/2024”, “1/15/2024”, A1:A5). This will give you the number of working days, excluding both weekends and holidays.

What If You Need to Customize Weekends?

The NETWORKDAYS function assumes a standard Monday-to-Friday workweek. But what if your weekends are different? For example, some countries have Friday and Saturday as weekends. In that case, you can use the NETWORKDAYS.INTL function. This function lets you customize which days are considered weekends.

Here’s the formula: =NETWORKDAYS.INTL(start_date, end_date, weekend_code, holidays). The weekend_code is a number that defines your weekend days. For example, “7” means Friday and Saturday are weekends. You can find a full list of codes in Excel’s help documentation.

Summarizing, counting days in Excel while excluding weekends and holidays is a game-changer for accurate scheduling. By using the NETWORKDAYS or NETWORKDAYS.INTL functions, you can easily calculate working days and keep your projects on track.

Thank you for reading! I hope this article helps you fully understand how to count days excluding weekends and holidays in Excel. If you have more questions or need further assistance, feel free to reach out to us. Happy Excel-ing!