Note: The other languages of the website are Google-translated. Back to English
登陆  \/ 
x
or
x
注册  \/ 
x

or

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

有时,我们需要知道两个日期之间有多少个特定的工作日。 例如,我有两个日期:开始日期是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用于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 for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能。 支持Office / Excel 2007-2019和365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能30天免费试用。 60天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
Say something here...
symbols left.
You are guest
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.
    Mohamed · 4 years ago
    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:

    [b]=[u]A1-WEEKDAY(A1)+5[/u][/b]

    Then if this Thursday falls before A1, we need to add 7 using

    [b](([u]A1-WEEKDAY(A1)+5[/u])
  • To post as a guest, your comment is unpublished.
    Jon · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    KAMBLE VIJAY · 5 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Mohamed · 5 years ago
      Let the start and end dates be in cells A1 and A2, respectively. This should work:

      =INT((A2-A1)/7)
  • To post as a guest, your comment is unpublished.
    Rob Mormile · 6 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Mohamed · 5 years ago
      Assuming cycle refers to month, this should work for the end date:

      DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY())>22,1,0),22)
  • To post as a guest, your comment is unpublished.
    Mohamed · 6 years ago
    Why are my comments not published completely????
    • To post as a guest, your comment is unpublished.
      Admin_jay · 6 years ago
      [quote name="Mohamed"]Why are my comments not published completely????[/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. :-)
  • To post as a guest, your comment is unpublished.
    Mohamed · 6 years ago
    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 [b]array[/b] 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
    • To post as a guest, your comment is unpublished.
      Mohamed · 6 years ago
      Sorry the formula above is not complete: It should be:

      =INT((WEEKDAY(StartDate-[b]2[/b])-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-[b]4[/b])-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-[b]6[/b])-StartDate+EndDate)/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
    • To post as a guest, your comment is unpublished.
      Mohamed · 6 years ago
      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
  • To post as a guest, your comment is unpublished.
    Jaco · 6 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Laura Tigers · 7 years ago
    Does not work properly. You need to consider what day you're starting from and ending with!
    • To post as a guest, your comment is unpublished.
      Mohamed · 6 years ago
      Hi Laura,
      Could you please elaborate?
  • To post as a guest, your comment is unpublished.
    PhilT · 7 years ago
    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
    • To post as a guest, your comment is unpublished.
      JamesB · 6 years ago
      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
      • To post as a guest, your comment is unpublished.
        Vasyl · 3 months ago
        THANK YOU VERY MUCH!!! This is perfect! This function exactly does the task! 
    • To post as a guest, your comment is unpublished.
      Mohamed · 7 years ago
      Try
      =INT((WEEKDAY($B$1-2)-$B$1+$B2)/7)+INT((WEEKDAY($B$1-4)-$B$1+$B2)/7)
      • To post as a guest, your comment is unpublished.
        Saravanan · 5 years ago
        Thank You. This is so useful.