跳到主要内容

如何在Excel中检查日期是否为公众假期并计算除假期外的天数?

作者:凯莉 最后修改时间:2020-04-30

例如,您有一个日期列表,并且想要检查列表中是否存在任何公共假日,如何快速完成? 在本文中,我将向您展示如何计算给定年份内美国公众假期的所有日期,然后轻松地在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)).

下表列出了计算所有公共假期的公式。 只需将公式输入适当的单元格,然后按 输入 一对一的关键。

假日生活 手机 公式
元旦 C2 =日期(C1,1,1)
马丁·路德·金小日 C3 = DATE(C1,1,1)+ 14 + CHOOSE(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2)
总统日 C4 = DATE(C1,2,1)+ 14 + CHOOSE(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 + CHOOSE(WEEKDAY(DATE(C1,10,1)),1,0,6,5,4,3,2)
退伍军人节 C9 =日期(C1,11,11)
感恩节 C10 = DATE(C1,11,1)+ 21 + CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5)
圣诞节 C11 =日期(C1,12,25)

请注意: 在上表的公式中,C1是定位给定年份的参考像元。 在我们的示例中,它表示2015年,您可以根据需要进行更改。

使用这些公式,您可以轻松计算给定年份的公共假期日期。 请参阅以下屏幕截图:

doc检查公众假期2

将范围另存为自动图文集词条(其余单元格格式和公式),以备将来重复使用

引用单元格并应用公式来计算每个假期一定是非常乏味的。 Kutools for Excel 提供了一个可爱的解决方法 自动文本 实用程序将范围另存为自动图文集词条,它可以保留范围中的单元格格式和公式。 然后,您只需单击一下即可重用此范围。 只需单击一下即可插入此表并更改此表中的年份,从而使工作变得容易!


广告汽车美国假期1

Kutools for Excel - 使用 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) 进入它,然后按 输入 键。

备注:在上述单元格中,E1是指定日期范围的开始日期,E2是结束日期,而B2:B10是我们在第1部分中计算的假日列表。

doc检查公众假期6

现在,您将获得指定日期范围内除周末和节假日以外的天数。

精确/静态复制公式,而无需在Excel中更改单元格引用

Kutools for Excel 确切的副本 实用程序可以帮助您轻松准确地精确复制多个公式,而无需在Excel中更改单元格引用,从而防止相对单元格引用自动更新。


广告完全复制公式3

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 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))
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations