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日,現在我想知道在此持續時間中星期日,星期一或星期二等發生了多少次。 也許這對我們來說有點困難,但是在這裡,我可以為您介紹一些有效的方法。


用公式計算兩個日期之間的特定工作日/週末的數量

假設我有以下兩個日期,我需要計算一下它們之間有多少個星期日。 選擇一個空白單元格,輸入以下公式,然後按 Enter 鍵。 現在,您將獲得兩個日期之間的星期天數。 看截圖:

=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月有多少個星期日,因此可以將此公式應用為以下公式之一,然後按 Enter 鍵,您將一次獲得幾個星期天。 看截圖:

=總天數(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 · 4 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 · 4 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 · 4 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 · 5 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 · 1 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.