跳到主要內容

如何計算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中日期範圍內所有周末​​,週末或星期幾的數量的公式。

Excel的Kutools - 包含 300 多個 Excel 基本工具。 享受全功能 30 天免費試用,無需信用卡! 現在下載!

1.選擇一個空白單元格,您將放置計數結果,然後單擊Kutools>公式助手>公式助手以啟用此功能

然後根據您的計數類型繼續。

A.計算Excel中兩個日期之間的周末(星期六和星期日)的數量

在“公式幫助器”對話框中,請執行以下操作:
(1)選擇 統計 來自 公式類型 下拉列表;
(2)點擊選擇 兩個日期之間的非工作天數 ,在 選擇一個公式 列錶框;
(3)在 開始日期 框(您也可以引用日期單元格);
(4)在 結束日期 框(您也可以引用日期單元格);
(5)點擊 OK 按鈕。

現在,它返回所選單元格中所有星期六和星期日的總數。

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

B.計算Excel中兩個日期之間的工作日數(不包括星期六和星期日)

在“公式幫助器”對話框中,請執行以下操作:
(1)選擇 統計 來自 公式類型 下拉列表;
(2)點擊選擇 兩個日期之間的工作天數 ,在 選擇一個公式 列錶框;
(3)在 開始日期 框(您也可以引用日期單元格);
(4)在 結束日期 框(您也可以引用日期單元格);
(5)點擊 OK 按鈕。

然後它返回所選單元格中的工作日總數(不包括星期六和星期日)。

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

C.計算Excel中兩個日期之間的特定星期幾(星期一,星期六,星期日等)的數量

在“公式幫助器”對話框中,請執行以下操作:
(1)選擇 統計 來自 公式類型 下拉列表;
(2)點擊選擇 計算特定工作日的數量 ,在 選擇一個公式 列錶框;
(3)在 開始日期 框(您也可以引用日期單元格);
(4)在 結束日期 框(您也可以引用日期單元格);
(5)用整數指定特定的工作日(1表示星期日,2-5表示星期一至星期五,7表示星期六);
(6)點擊 OK 按鈕。

然後返回給定日期範圍內指定工作日的總數。

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 (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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations