Excel公式技巧35: 計算工作日天數

2021-02-13 完美Excel

學習Excel技術,關注微信公眾號:

excelperfect

 

本文提供了一個公式,能夠計算多種情形下某個月的工作日天數,如下圖1所示。

圖1

 

單元格C2中的公式為:

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,0))))

向右拖至F列,向下拖至第8行。

 

NETWORKDAYS函數的語法為:

NETWORKDAYS(start_date,end_date, [holidays])

其中,參數start_date必需,指定開始日期;參數end_date必需,指定結束日期;參數holidays,可選,指定要排除的日期。

 

因此,參數start_date是公式中的:

MAX($A2,C$1)

獲得開始日期和當前月首日中較大的值。

 

參數end_date是公式中的:

MIN($B2,EOMONTH(C$1,0))

其中的EOMONTH(C$1,0)獲取當前月最後一天的日期值,因此獲取結束日期和當前月最後一天日期值中的較小值。

 

這樣,將本月中的開始和結束日期就確定了,傳遞給NETWORKDAYS函數得到當月工作日天數。

 

在第9行中,我們將假期排除在外,單元格C9中的公式為:

=MAX(0,NETWORKDAYS(MAX($A9,C$1),MIN($B9,EOMONTH(C$1,0)),$B$13:$B$15))

向右拖至F列。

 

公式中添加了NETWORKDAYS函數的可選參數holidays,將指定的日期排除。

 

在Excel 2010中,Microsoft引入了一個新函數NETWORKDAYS.INTL,增加了能夠自定義周末日期的功能。其語法為:

NETWORKDAYS.INTL(start_date,end_date, [weekend], [holidays])

其中,參數weekend可指定下表中的值:

圖2

參數weekend也可以指定由0和1組成的7個字符長的字符串,其每個字符代表一周中的某一天,從周一開始。1代表該天是非工作日,0代表工作日。例如,0000011表示周末是周六和周日。注意,在字符串中只能有1和0,且使用1111111將總是返回0。

 

對於我們所舉的示例來說,如果在單元格C9中輸入公式:

=MAX(0,NETWORKDAYS.INTL(MAX($A9,C$1),MIN($B9,EOMONTH(C$1,0)),6, $B$13:$B$15))

Excel將會認為周四和周五是周末。

 

使用公式:

=MAX(0,NETWORKDAYS.INTL(MAX($A9,C$1),MIN($B9,EOMONTH(C$1,0)),"1010100", $B$13:$B$15))

Excel將認為周一、周三、周五是周末。

相關焦點

  • Excel公式詳解:月損失時間計算
    拋去系統資料庫要加欄位、界面要重做、導入導出要重做等等僅增加工作量的任務不談,更麻煩的是用系統實現計算的話要加一系列的邏輯,各種校驗、計算等等,伴隨需求的變化,邏輯裡面的一些數值最好還得做成可配置……為了變更管理制度方便,為了用戶錄入方便,為了開發方便,為了維護方便,為了程式設計師的發量,更重要的是為了提升一下Excel水平,我決定換個思路,在Excel裡面算當月損失時間,系統只做計算結果的導入
  • Excel函數公式:Excel 2016新增函數DATEDIF超級實用技巧解讀
    時間日期,是我們日常辦公中必不可少的部分,如何快速的計算兩個日期之間相差的年、月、日等,在Excel2016版本以前,是比較難以實現的,但是如果用Excel 2016 的新增函數DATEDIF來計算,將會非常的簡單。
  • 如何用Excel快速算出員工當月工作天數?
    每個月,公司有新入職員工,也有老員工,算工作日天數一個一個算,如果新員工人數少還可以湊合,人數多了,特別是勞動密集型企業,不但累,還容易出錯。這裡小匯告訴大家一個簡單的公式,快速算出員工的工作天數。不過值得注意的是,統計情況不含法定假日、加班等情況。下面是2015年1月份的工資表局部。
  • Excel公式技巧53: 使用TEXTJOIN函數反轉文本
    學習Excel技術,關注微信公眾號:excelperfect 在《Excel公式技巧48:生成從大到小連續的整數
  • Excel公式練習15:求2018年母親節的日期
    微信公眾號:excelperfect本次的練習是:如何使用公式求出2018年母親節的日期?
  • Excel函數公式:實用技巧、用名稱計算、給公式添加備註,你確定不來看看
    一、公式中添加備註。目的:將「年末福利紅包做到計算到年末工資表中」,並加備註。方法:在計算公式的末尾添加:+N("說明性文字")。解讀:函數N可以將字符串轉換為0,所以+N("說明性文字")附加到公式中,其結果不變。二、用名稱計算結果。目的:用名稱參與公式的計算。
  • Excel教程:最常用日期函數匯總(收藏篇)
    公式結果返回44138,這是日期的序列號形式,可將格式設置為日期格式函數EOMONTH:返回一串日期,表示指定月數之前或之後的月份的最後一天。例:根據A列的日期,返回該日期當月最後一天的日期。函數EOMONTH的第二參數為0,即返回當前日期所在月份最後一天的日期。函數NETWORKDAYS:返回兩個日期之間的完整工作日數。
  • Excel函數公式:邏輯函數IF、AND、OR、NOT、IFERROR實用技巧解讀
    方法:在目標單元格中輸入公式:=IF(NOT(E3>=60),"不及格","及格")。解讀:1、條件NOT(E3>=60)等同於C3<60,公式:=IF(NOT(E3>=60),"不及格","及格")就可以變換為:=IF(C3<60,"不及格","及格")。2、NOT函數的作用就可以理解為「相反」。6、目的:隱藏公式以外的其它錯誤。
  • Excel實用技巧:如何不顯示報表中的0值
    問題是如何讓excel報表裡的數字0不顯示呢?    今天和大家一起分享一下這方面的技巧。 一、所有0值顯示為空    在Excel2007中,我們可以通過Excel選項將所有0顯示為空。二、僅僅將公式或函數返回的0值顯示為空    要將公式或函數範圍的0值顯示為空,只需要嵌套一個IF函數就可以了。
  • Excel教程:Excel日期問題的小妙招
    3、怎麼計算兩個日期間的間隔天數?輸入公式:=DATEDIF(A2,B2,"d")DATEDIF函數:是Excel中一個隱藏的日期函數,計算兩個日期間的年、月、天數。DATEDIF(開始日期,結束日期,返回的時間單位代碼)返回的是天數,所以第三參數為「d」。
  • Excel函數公式:SUMIF函數使用技巧範例合集
    二、計算「OPPO」的銷量。方法:在對應的目標單元格中輸入公式:=SUMIF(C3:C11,"OPPO",D3:D11)。三、計算除「OPPO」之外的銷量。四、計算大於「50」的銷量之和。五、計算低於平均值的銷量之和。
  • Excel教程:函數VLOOKUP實用技巧
    下面我就和大家分享一下VLOOKUP函數的一些使用技巧,解決這些貌似不能的問題。首先,先來看下VLOOKUP的最基礎用法,為了方便大家理解,做成了圖片。進行數組擴展後,三個參數都變成十三行兩列的數組,各有26個元素:於是我們可以確定:這個數組公式需要重複計算26次,並返回一個十三行兩列的數組。
  • Excel教程:事半功倍的幾個Excel技巧
    解決方法:區域內輸入公式=RANDBETWEEN(1000,2000),下拉技巧二:文本數據求和這位同學的提問,設置了文本的數據沒法用SUM公式求和比如從系統導出來的表格這就需要用到下面這個技巧了技巧三:複製選擇性粘貼先在旁邊一單元格輸入1(單元格最好是設置了邊框,否則會影響要轉化區域內的邊框樣式),然後複製它,選擇文本區域,右鍵,選擇性粘貼,乘,區域內的文本就變成常規數格式了。
  • 258.Office技巧:如何用Excel計算一個人的年齡?
    上面說了那麼多,其實就是獨裁通知,不知道是不是和我們歷史那個姓贏的差不多…… 一看這個Office問題留言的小夥伴肯定是HR,一個非常經典的Excel案例。趕緊與大家分享一下。場景:適合HRT人事等需要用Excel進行數據整理的辦公人士。問題:如何用Excel計算一個人的年齡?
  • 每天學一點excel:IF函數的使用方法
    點擊上方藍色 每天學一點excel ,關注後獲得更多excel教程和技巧。大家好,今天小慄教大家怎麼使用if函數。
  • Excel公式練習21:在單元格列區域中輸入連續的數字
    微信公眾號:excelperfect本文系因違規而刪除的2017年11月5日推送文章經修改後重新推送,已看過的朋友可直接飄過
  • Excel公式練習11:顛倒單元格區域中的數據
    微信公眾號:excelperfect 本次的練習是:
  • Excel進階:如何自定義一個函數?
    假如你經常使用這個函數組合,而又不想記住這些複雜的公式,你就需要自己發明一個函數了。例如,要求地球上兩個點的距離,函數組合是這樣的:於是我們就在想,要是有個函數可以計算距離就好了,好吧,我們動手自定義一個函數吧。
  • Excel公式練習3:求連續數據之和的最大值
    微信公眾號:excelperfect 本次的練習是:
  • 函數 | Datedif/Today計算年齡不求人
    不過呢,在 Excel 裡計算年齡可是很常見的一項工作,你會嗎?接下來教你怎麼通過出生日期計算出女神的真實年齡、虛歲。01自動算周歲這是最簡單的,只要知道今天的日期和出生日期,Excel 就可以輕鬆幫我們算出女神們的具體年齡。