## How to check if a date is public holiday and count days except holidays in Excel?

For example, you have a list of dates and want to check if any public holidays exist in the list, how could you get it done quickly? In this article I will show you the ways to calculate all dates of American public holidays within given years, and then check if a date is an American public holiday in Excel easily.

#### Part 1: Calculate public holidays with given year in Excel

Before checking if a date is a public holiday, you must list all holidays within given years in Excel. Therefore preparing a table as following screen shot shown will make your work easier.

There are three kinds of American public holidays:

(1) The first kind is a public holiday on a fixed date, such as New Year Day is on Jan 1st. we can easily calculate the New Year Day with the formula =DATE(Given Year,1,1);

(2) The second kind is a public holiday on a fixed weekday, such as President Day. We can easily calculate the President day with the formula =DATE(Given Year,1,1)+14+CHOOSE(WEEKDAY(DATE(Given Year,1,1)),1,0,6,5,4,3,2);

(3) And the last kind is the Memorial Day, we can easily calculate the Memorial Day with the formula =DATE(Given Year,6,1)-WEEKDAY(DATE(Given Year,6,6)).

Here I list formulas to calculate all public holidays in the following table. Just enter the formulas into the proper cell and press Enter key one by one.

 Holiday Cell Formulas New Year Day C2 =DATE(C1,1,1) Martin Luther King Jr. Day C3 =DATE(C1,1,1)+14+CHOOSE(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2) President Day C4 =DATE(C1,2,1)+14+CHOOSE(WEEKDAY(DATE(C1,2,1)),1,0,6,5,4,3,2) Memorial Day C5 =DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6)) Independence Day C6 =DATE(C1,7,4) Labor Day C7 =DATE(C1,9,1)+CHOOSE(WEEKDAY(DATE(C1,9,1)),1,0,6,5,4,3,2) Columbus Day C8 =DATE(C1,10,1)+7+CHOOSE(WEEKDAY(DATE(C1,10,1)),1,0,6,5,4,3,2) Veterans Day C9 =DATE(C1,11,11) Thanksgiving Day C10 =DATE(C1,11,1)+21+CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5) Christmas Day C11 =DATE(C1,12,25)

Note: In the formulas in above table, the C1 is the reference cell which locates the given year. In our example, it means Year 2015, and you can change it based on your needs.

With these formulas, you can easily calculate dates of the public holidays with given years. See screen shot below:

#### Save a range as AutoText entry (remaining cell formats and formulas) for reusing in future

It must be very tedious to refer cells and apply formulas for calculating each holiday. Kutools for Excel provides a cute workaround of AutoText utility to to save the range as an AutoText entry, which can remain the cell formats and formulas in the range. And then you will reuse this range with just one click. Work becomes easy by only one clicking to insert this table and change the year in this table!

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now

#### Part 2: Check if a date is public holiday in Excel

After listing all dates of public holidays of specific years, we can easily check if a date is public holiday or not with formulas in Excel. Supposing you have a date list as following screen shot shown, and I will introduce the ways to get it done easily.

Select a blank cell besides the date list, says Cell B18, enter the formula =IF(COUNTIF(\$C\$2:\$D\$11,A18),"Holiday","No") into it, and then drag the Fill Handle to the range you need. See screenshot above:

Notes:

(1) In the formula =IF(COUNTIF(\$C\$2:\$D\$11,A18),"Holiday","No"), the \$C\$2:\$D\$11 is the range of public holidays in specific years, and A18 is the cell with date you want to check if it's a public holiday, and you can change them based on your needs. And this formula will return "Holiday" if the specific date is a public holiday, and return "No" if it's not.

(2) You can also apply this array formula =IF(OR(\$C\$2:\$D\$11=A18),"Holiday","NO") to check if corresponding date is holiday or not.

#### Part 3: Count days between two days except weekends and holidays in Excel

In Part 1 we have listed all holidays in a given year, and now this method will guide you to count the number of days except all weekends and holidays in a date range.

Select a blank cell you will return the number of days, and enter the formula =NETWORKDAYS(E1,E2,B2:B10) into it, and press the Enter key.

Note: In above cells, E1 is the start date of specified date range, E2 is the end date, and B2:B10 is the holiday list we calculate in Part 1.

Now you will get the number of days except weekends and holidays in the specified date range.

#### Copy formulas exactly/ statically without changing cell references in Excel

Kutools for Excel Exact Copy utility can help you easily copy multiple formulas exactly without changing cell references in Excel, preventing relative cell references updating automatically.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required!Get It Now

### Best Office Productivity Tools

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |  Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |  Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
This comment was minimized by the moderator on the site
Like the Observed Holiday, I need to also recognize additional days off outside of the Holiday or observed holidays. Example: If Christmas is on a Friday, I need to calculate the days before and after (until New Years) off.
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX(\$A\$1:\$A\$11,MATCH(A18,\$C\$1:\$C\$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF(\$C\$2:\$C\$11,A18),INDEX(\$A\$1:\$A\$11,MATCH(A18,\$C\$1:\$C\$11,0)),"No")

Please note that the dates you will check should be placed in one column.
There are no comments posted here yet