跳到主要內容

如何在Excel中檢查日期是否為公眾假期併計算除假期外的天數?

例如,您有一個日期列表,並且想要檢查列表中是否存在任何公共假日,如何快速完成? 在本文中,我將向您展示如何計算給定年份內美國公眾假期的所有日期,然後輕鬆地在Excel中檢查日期是否為美國公眾假期。


第1部分:在Excel中計算給定年份的公共假期

在檢查日期是否為公眾假期之前,必須在Excel中列出給定年份內的所有假期。 因此,按照下面的屏幕快照所示準備表格將使您的工作更加輕鬆。

doc檢查公眾假期1

美國有三種公共假期:

(1)第一種是固定日期的公共假期,例如1月XNUMX日為元旦。 我們可以輕鬆地用公式計算元旦 = DATE(給定年份,1,1);

(2)第二種是在固定工作日的公共假日,例如“總統日”。 我們可以輕鬆地用公式計算總統日 = DATE(Given Year,1,1)+ 14 + CHOOSE(WEEKDAY(DATE(Given Year,1,1)),1,0,6,5,4,3,2);

(3)最後一種是陣亡將士紀念日,我們可以使用以下公式輕鬆計算陣亡將士紀念日 = DATE(Given Year,6,1)-WEEKDAY(DATE(Given Year,6,6)).

下表中列出了用於計算所有公共假期的公式。 只需將公式輸入適當的單元格,然後按 Enter 一對一的關鍵。

節日 細胞 公式
元旦 C2 =日期(C1,1,1)
馬丁·路德·金小日 C3 = DATE(C1,1,1)+14+選擇(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2)
總統日 C4 = DATE(C1,2,1)+14+選擇(WEEKDAY(DATE(C1,2,1)),1,0,6,5,4,3,2)
紀念日 C5 = DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6))
獨立日 C6 =日期(C1,7,4)
勞動節 C7 = DATE(C1,9,1)+ CHOOSE(WEEKDAY(DATE(C1,9,1)),1,0,6,5,4,3,2)
哥倫布日 C8 = DATE(C1,10,1)+7+選擇(WEEKDAY(DATE(C1,10,1)),1,0,6,5,4,3,2)
退伍軍人節 C9 =日期(C1,11,11)
感恩節 C10 = DATE(C1,11,1)+21+選擇(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5)
聖誕節 C11 =日期(C1,12,25)

注意: 在上表的公式中,C1是定位給定年份的參考像元。 在我們的示例中,它表示2015年,您可以根據需要進行更改。

使用這些公式,您可以輕鬆計算給定年份的公共假期日期。 請參閱以下屏幕截圖:

doc檢查公眾假期2

將範圍另存為自動圖文集詞條(其餘單元格格式和公式),以備將來重複使用

引用單元格並應用公式來計算每個假期必須非常繁瑣。 Kutools for Excel提供了一個可愛的解決方法 自動文本 實用程序可將範圍另存為自動圖文集條目,該條目可以保留範圍中的單元格格式和公式。 然後,您只需單擊一下即可重用此範圍。 只需單擊一下即可插入此表並更改此表中的年份,從而使工作變得容易!


廣告汽車美國假期1

Excel的Kutools - 使用 300 多種基本工具增強 Excel 功能。 享受全功能 30 天免費試用,無需信用卡! 立即行動吧!

第2部分:在Excel中檢查日期是否為公眾假期

在列出特定年份的所有公共假期的所有日期之後,我們可以使用Excel中的公式輕鬆檢查日期是否為公共假期。 假設您有一個日期列表,如下面的屏幕截圖所示,我將介紹如何輕鬆完成它。

單元格B18說,除了日期列表外,選擇一個空白單元格,輸入公式 = IF(COUNTIF($ C $ 2:$ D $ 11,A18),“ Holiday”,“ No”) 進入它,然後將“填充手柄”拖動到所需的範圍。 參見上面的截圖:

筆記:

(1)在公式= IF(COUNTIF($ C $ 2:$ D $ 11,A18),“ Holiday”,“ No”)中,$ C $ 2:$ D $ 11是特定年份的公共假期範圍,並且A18是您要檢查其是否是公共假日的日期的單元格,您可以根據需要進行更改。 如果特定日期是公共假日,則此公式將返回“假日”,如果不是,則返回“否”。

(2)您也可以應用此數組公式 = IF(OR($ C $ 2:$ D $ 11 = A18),“ Holiday”,“ NO”) 檢查相應的日期是否是假期。


第3部分:在Excel中計算兩天之間的天,週末和節假日除外

在第1部分中,我們列出了給定年份中的所有假期,現在該方法將指導您計算日期範圍內除所有周末和假期之外的天數。

選擇一個空白單元格,您將返回天數,然後輸入公式 = NETWORKDAYS(E1,E2,B2:B10) 進入它,然後按 Enter 鍵。

備註:在上述單元格中,E1是指定日期範圍的開始日期,E2是結束日期,而B2:B10是我們在第1部分中計算的假日列表。

doc檢查公眾假期6

現在,您將獲得指定日期範圍內除週末和節假日以外的天數。

精確/靜態復制公式,而無需更改Excel中的單元格引用

Excel的Kutools 確切的副本 實用程序可以幫助您輕鬆準確地複制多個公式,而無需在Excel中更改單元格引用,從而防止相對單元格引用自動更新。


廣告完全複製公式3

Excel的Kutools - 使用 300 多種基本工具增強 Excel 功能。 享受全功能 30 天免費試用,無需信用卡! 立即行動吧!

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (9)
No ratings yet. Be the first to rate!
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.
This comment was minimized by the moderator on the site
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 :-)
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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))
This comment was minimized by the moderator on the site
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)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations