How to calculate and check if a day is a holiday or not in Excel

When working with schedules, payroll, or project planning in Excel, it’s essential to calculate and check if a day is a holiday to avoid miscalculations. Whether you’re managing business workdays, personal plans, or government holidays, Excel provides powerful functions to automate this process. In this guide, we’ll explore how to efficiently verify if a given date is a holiday and count workdays while excluding holidays and weekends.

Part 1: Listing Holidays in a Specific Year

Before checking whether a date is a holiday, you must first create a list of holidays. Holidays in the U.S. typically fall into three categories:

• Fixed-date holidays (e.g., New Year’s Day on January 1).
• Floating-date holidays (e.g., Presidents’ Day, which falls on the third Monday of February).
• Last-weekday holidays (e.g., Memorial Day, which falls on the last Monday of May).

Holiday Calculation Formulas

Use the following formulas in Excel to calculate major U.S. holidays for a given year (assuming the year is in cell A1):

Note: A1 represents the year you are calculating holidays for. Change it accordingly.

Example: Public holidays in 2025

Part 2: Checking If a Date Is a Holiday

Once you have a list of holiday dates, you can check if a specific date is a holiday using the COUNTIF function.

Recipe

1. Enter your list of holidays (from Part 1) into a range, e.g., B2:B11.
2. Enter the date you want to check in cell A2.
3. Use the following formula in a new cell to check if A2 is a holiday:

   =IF(COUNTIF($B$2:$B$11, A2), "Holiday", "Not a Holiday")
   

4. If the date in A2 matches any date in B2:B11, it will return “Holiday”, otherwise, it will return “Not a Holiday”.

• $B$2:$B$11 is a list of holidays.
• A2 is the date to check.
• If the date is in the list, the formula returns “Holiday”.

Part 3: Counting Days Between Two Dates Excluding Weekends and Holidays

You can calculate the number of working days between two dates while excluding weekends and holidays using the NETWORKDAYS function.

Recipe

1. Enter the start date in E1 and the end date in E2.
2. Use the holiday list from C2:C11.
3. Apply the following formula:

   =NETWORKDAYS(E1, E2, C2:C11)
   

4. This will return the number of working days between E1 and E2, excluding weekends and the specified holidays.

• E1 is the start date.
• E2 is the end date.
• $B$2:$B$11 is the list of holidays.

Example:

If E1 = 01/01/2025 and E2 = 01/10/2025, and assuming January 1st is a holiday, the formula will return 6 working days instead of 7.

Part 4: Counting Workdays While Excluding Custom Weekends and Holidays

The NETWORKDAYS.INTL function in Excel helps calculate the number of workdays between two dates while allowing you to exclude customizable weekends and holidays.

Basic Formula:

Explanation:

• A2, B2: Start and end dates.
• 1: Weekend type (Saturday-Sunday by default; can be changed).
• $C$2:$C$11: A list of holidays to exclude.

Customizing the Weekend:

This function allows you to customize which days are considered weekends:

• 1 → Saturday – Sunday (Default).
• 2 → Sunday – Monday.
• 11 → Sunday only.
• 7 → Friday – Saturday (common in some Middle Eastern countries).

Example:

Excludes both Friday – Saturday and the listed holidays.

• See more: Skipping Holidays and sundays in excel formula

Example for 2025:

Let’s assume the following table:

If we want to count the workdays between 02/01/2025 and 02/20/2025, excluding Saturday-Sunday and holidays, we use:

Result: Excel will automatically exclude holidays and weekends, giving you an accurate workday count.

When to Use NETWORKDAYS vs. NETWORKDAYS.INTL?

Conclusion

Hopefully, through this article, you can easily calculate and check whether a day is a holiday or not, ensuring accurate scheduling and time management. Whether you need a quick check or a detailed workday count with custom weekends, these methods will help streamline your workflow. Try implementing them in your Excel spreadsheets and save time on manual date verification!

See more: Top 3 Quickest Methods to Count Cells in Excel