Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 within given years in Excel

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

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

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. Full Feature Free Trial 30-day!

ad exact copy formulas 3

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubblePart 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.

doc check if public holiday 1

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:

doc check if public holiday 2


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! Full Feature Free Trial 30-day!

ad auto american holidays 1

arrow blue right bubblePart 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.


arrow blue right bubble 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.

doc check if public holiday 6

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


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    KC · 1 years ago
    How to make it show the actual name of the holiday instead of just "holiday"?
    • To post as a guest, your comment is unpublished.
      kellytte · 11 months ago
      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.
  • To post as a guest, your comment is unpublished.
    Shawn Bull · 2 years ago
    Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative.
    The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays.
    An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "0"
    21/12/2017 4 1
    22/12/2017 5 1
    23/12/2017 6
    24/12/2017 7
    25/12/2017 1 0
    26/12/2017 2 0
    27/12/2017 3 1
    28/12/2017 4 1
    29/12/2017 5 1
    30/12/2017 6
    31/12/2017 7

    I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula.
    The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month

    Mon Tue Thu Fri
    7 8 10 11
    Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells.

    I hope I am explaining with sufficient clarity :-)
  • To post as a guest, your comment is unpublished.
    Mark · 2 years ago
    How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
    • To post as a guest, your comment is unpublished.
      Tboo Ted Marshall · 2 years ago
      I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
      • To post as a guest, your comment is unpublished.
        Josh · 1 years ago
        This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
  • To post as a guest, your comment is unpublished.
    LINDA · 3 years ago
    trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays.

    here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.]

    =WORKDAY(B28-5,1,HOLIDAYS)