跳到主要内容

如何计算Excel中两个日期之间的周末/工作日数?

作者:晓阳 最后修改时间:2020-07-03

有时,我们需要知道两个日期之间有多少个特定的工作日。 例如,我有两个日期:开始日期是1年1月2014日,结束日期是2年15月2014日,现在我想知道在此持续时间中星期日,星期一或星期二等发生了多少次。 也许这对我们来说有点困难,但是在这里,我可以为您介绍一些有效的方法。


用公式计算两个日期之间特定工作日/周末的数量

假设我有以下两个日期,我需要计算一下它们之间有多少个星期日。 选择一个空白单元格,输入以下公式,然后按 输入 键。 现在,您将获得两个日期之间的星期天数。 看截图:

=INT((WEEKDAY($C$2- 1)-$C$2+$C3)/7)

笔记:

(1)在上式中,C2是开始日期,C3是结束日期。

(2)在上式中 1 代表星期日。 并且您可以将数字1替换为1到7之间的其他数字。1是星期日,2是星期一,3是星期二,4是星期三,5是星期四,6是星期五,7是星期六)


使用公式计算月份中特定工作日的数量

有时,您可能需要计算给定月份中某个工作日的总数,例如计算2020年XNUMX月星期三的总数。在这里,我将引入一个公式来计算指定月份中某个工作日的总数一个月轻松。

选择一个空白单元格,键入下面的公式,然后按Enter键以获取计数结果。

=INT((WEEKDAY(DATE(G2,G3,1)- G4)-DATE(G2,G3,1)+EOMONTH(DATE(G2,G3,1),0))/7)

:

(1)在上式中,G2是指定的年份,G3是指定的月份,G4是指定的星期几。

(2)此公式指定代表星期几的整数:1是星期日,2是星期一,3是星期二,4是星期三,5是星期四,6是星期五,7是星期六。


使用用户定义的功能来计算给定月份中特定工作日/周末的数量

除上述公式外,您还可以创建用户定义函数来计算给定年份和月份中的特定星期几。

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications窗口.

2。 点击 插页 > 模块,然后将以下代码粘贴到 模块窗口.

Public Function TotalDays(pYear As Integer, pMonth As Integer, pDay As Integer)
'Update 20140210
Dim xindex As Integer
Dim endDate As Integer
endDate = Day(DateSerial(pYear, pMonth + 1, 0))
For xindex = 1 To endDate
    If Weekday(DateSerial(pYear, pMonth, xindex)) = pDay Then
        TotalDays = TotalDays + 1
    End If
Next
End Function

3。 保存此代码并返回到工作表,然后在空白单元格中输入此公式 = TotalDays(年,月,1) 。 在此示例中,我将计算2020年XNUMX月有多少个星期日,因此我可以将此公式应用为以下公式之一,然后按 输入 键,您将一次获得几个星期天。 查看屏幕截图:

=总天数(C2,C3,C4)

=总天数(2020,6,1)

 

笔记: 此公式使用整数表示星期几: 1是星期日,2是星期一,3是星期二,4是星期三,5是星期四,6是星期五,7是星期六.


使用 Kutools for Excel 计算两个日期之间所有周末/工作日/一周中特定日期的数量

实际上,我们可以应用Kutools for Excel 两个日期之间的非工作天数 式, 两个日期之间的工作天数 公式,以及 计算特定工作日的数量 快速计算Excel中日期范围内所有周末,周末或一周中特定天数的公式。

Kutools for Excel - 包含 300 多个 Excel 基本工具。 享受全功能 30 天免费试用,无需信用卡! 现在下载!

1.选择一个空白单元格,您将放置计数结果,然后单击Kutools>公式助手>公式助手以启用此功能

然后根据您的计数类型继续。

A.计算Excel中两个日期之间的周末(星期六和星期日)的数量

在“公式帮助器”对话框中,请执行以下操作:
(1)选择 统计 来自 公式类型 下拉列表;
(2)点击选择 两个日期之间的非工作天数 ,在 选择一个公式 列表框;
(3)在 开始日期 框(您也可以引用日期单元格);
(4)在 结束日期 框(您也可以引用日期单元格);
(5)点击 OK 按钮。

现在,它返回所选单元格中所有星期六和星期日的总数。

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

B.计算Excel中两个日期之间的工作日数(不包括星期六和星期日)

在“公式帮助器”对话框中,请执行以下操作:
(1)选择 统计 来自 公式类型 下拉列表;
(2)点击选择 两个日期之间的工作天数 ,在 选择一个公式 列表框;
(3)在 开始日期 框(您也可以引用日期单元格);
(4)在 结束日期 框(您也可以引用日期单元格);
(5)点击 OK 按钮。

然后返回所选单元格中的工作日总数(不包括周六和周日)。

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

C.计算Excel中两个日期之间的特定星期几(星期一,星期六,星期日等)的数量

在“公式帮助器”对话框中,请执行以下操作:
(1)选择 统计 来自 公式类型 下拉列表;
(2)点击选择 计算特定工作日的数量 ,在 选择一个公式 列表框;
(3)在 开始日期 框(您也可以引用日期单元格);
(4)在 结束日期 框(您也可以引用日期单元格);
(5)用整数指定特定的工作日(1表示星期日,2-5表示星期一至星期五,7表示星期六);
(6)点击 OK 按钮。

然后返回给定日期范围内指定工作日的总数。

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 (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Let's say you want to calculate the 3rd Thursday of the month and assume the date of the first of the month is in cell A1. We need to first work out the date of the Thursday in the week of A1. Because Thursday is the 5th day of the week, we use: =A1-WEEKDAY(A1)+5 Then if this Thursday falls before A1, we need to add 7 using [b]((A1-WEEKDAY(A1)+5)
This comment was minimized by the moderator on the site
What about a formula that returns the actual date of the first Wed of each month, Or the 2nd and 3rd tuesday of each month? Or every 3rd tuesday? I want to be able to put in my own start and end dates and then get the actual DATES (not the count) returned to me. Any ideas?
This comment was minimized by the moderator on the site
hi sir, i want to calculate no. of weeks in excel between two dates, but Dose not see right weeks as per date for example: I enterd the date 01/01/2016 ( Friday) and second date 14/01/2016( thursday) no of weeks showing = 2 weeks. but i want to show exact 2 weeks completed 15/01/2016 other wise show previse no fo weeks.
This comment was minimized by the moderator on the site
Let the start and end dates be in cells A1 and A2, respectively. This should work: =INT((A2-A1)/7)
This comment was minimized by the moderator on the site
Is it possible to have the start date set to "=today()" and the end date, for example, the 22nd of the current cycle. As the date returns to the 23rd, refresh the formula to the following 22nd?
This comment was minimized by the moderator on the site
Assuming cycle refers to month, this should work for the end date: DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY())>22,1,0),22)
This comment was minimized by the moderator on the site
Why are my comments not published completely????
This comment was minimized by the moderator on the site
[quote]Why are my comments not published completely????By Mohamed[/quote] Sorry, please try to send me the formula to jaychivo#extendoffice.com. Please replace @ with #. And i will help you post it. May be there are some characters which have been blocked. :-)
This comment was minimized by the moderator on the site
Hi Jaco, You may achieve this for someone who works Mondays (2), Wednesdays (4) and Fridays (6) as follows: (1) Call the year's start and end dates [quote]StartDate[/quote] and [quote]EndDate[/quote], respectively. (2) List all the public holidays in South Africa (this could span more than one year) in a range and call it [quote]PublicHolidays[/quote] (3) To calculate the total number of days worked enter the following array formula: [quote]=INT((WEEKDAY(StartDate-2)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate )/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
This comment was minimized by the moderator on the site
Sorry the formula above is not complete: It should be: =INT((WEEKDAY(StartDate-2)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate)/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
This comment was minimized by the moderator on the site
I don't know what happened to my formula and the rest of my message above. The formula should be: =INT((WEEKDAY(StartDate-2)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate )/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
This comment was minimized by the moderator on the site
Hi, I am from South Africa and I need advice. I have two workers at work who works different days. Now I want to type in a formula in excel to count how many days a year she work (that I can do), but the trick comes in when I want to type in a formula which allows me to deduct if one of her working days is a public holiday for example she works Monday, Wednesday and Friday. That means she works 156 days per year, but I want excel to deduct the holidays if it is on one of her working days. Can someone please assist me?
This comment was minimized by the moderator on the site
Does not work properly. You need to consider what day you're starting from and ending with!
This comment was minimized by the moderator on the site
Hi Laura, Could you please elaborate?
This comment was minimized by the moderator on the site
Thank you for this. Question, how do I add another day like "Wednesday or 4" to the Monday? Basically I want it to calculate both the total of Mondays and Wednesdays between the two dates. How do I write this formula? Thanks again
This comment was minimized by the moderator on the site
To help future seekers. Use this formula for calculating days between two dates: =NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] ) =NETWORKDAYS.INTL(A3,A4,"00000011",C3:C8) - 0=include day 1=exclude day
This comment was minimized by the moderator on the site
THANK YOU VERY MUCH!!! This is perfect! This function exactly does the task!
This comment was minimized by the moderator on the site
Try =INT((WEEKDAY($B$1-2)-$B$1+$B2)/7)+INT((WEEKDAY($B$1-4)-$B$1+$B2)/7)
This comment was minimized by the moderator on the site
Thank You. This is so useful.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations