計(jì)算一段日期內(nèi)的應(yīng)出勤天數(shù)是工作中經(jīng)常遇到的問題, 不僅需要考慮周末, 還要考慮國(guó)家的法定節(jié)假日和調(diào)休等.
在日常的工作中有以下四種情況:
- 1 雙休, 不考慮節(jié)假日和調(diào)休
- 2 雙休, 考慮節(jié)假日, 但不考慮調(diào)休
- 3 單休, 不考慮法定節(jié)假日和調(diào)休
- 4 雙休或單休, 考慮法定節(jié)假日和調(diào)休
為了計(jì)算上面的四種情況, 需要用到Excel中的兩個(gè)函數(shù): NETWORKDAYS和NETWORKDAYS.INTL. 接下來首先介紹四種情況的解決方法, 最后介紹兩個(gè)函數(shù)的詳細(xì)使用方法.
1 雙休, 不考慮節(jié)假日和調(diào)休
這種情況下, 所有的周六和周日都是休息日, 除周六和周日外的日期都是正常工作日. 這時(shí)可以使用NETWORKDAYS函數(shù)可以解決.
NETWORKDAYS的使用方法是: NETWORKDAYS(start_date, end_date, [holidays]), 函數(shù)包含三個(gè)參數(shù):
- start_date, 必選參數(shù), 表示開始日期;
- end_date, 必選參數(shù), 表述結(jié)束日期;
- holidays, 可選參數(shù), 表示節(jié)假日
計(jì)算示例如下
- 計(jì)算
2018-12-03~2018-12-31之間的工作日天數(shù), 就可以使用公式NETWORKDAYS("2018-12-3", "2018-12-31"). - 針對(duì)單元格的計(jì)算如下圖所示.
C2單元格的工作日天數(shù)的計(jì)算公式是NETWORKDAYS(A2,B2), 其中A2和B2分別表示開始日期和結(jié)束日期,
計(jì)算兩個(gè)日期之間的工作日天數(shù) - 計(jì)算給定日期所在月份剩余的工作日天數(shù):
NETWORKDAYS(A2,DATE(YEAR(A2),MONTH(A2)+1,)), 其中A2單元格表示日期,DATE(YEAR(A2),MONTH(A2)+1,)用于計(jì)算該月的最后一天(如2018-12-3, 12月的最后一天是2018-12-31)
計(jì)算給定日期所在月份剩余的工作日天數(shù)
2 雙休, 考慮節(jié)假日, 但不考慮調(diào)休
這種情況下所有的周末和指定的節(jié)假日是休息日, 其余時(shí)間為正常的工作日, 示例如下: C2單元格的計(jì)算公式為: NETWORKDAYS(A2,B2,$E$2:$E$6), 其中A2和B2分別表示開始日期和結(jié)束日期, 第三個(gè)參數(shù)$E$2:$E$6表示法定節(jié)假日.
3 單休, 不考慮法定節(jié)假日和調(diào)休
單休的情況下就不能使用NETWORKDAYS函數(shù)了, 需要使用另外一個(gè)函數(shù)NETWORKDAYS.INTL, 該函數(shù)包括四個(gè)參數(shù), 分別是:
- start_date, 必選參數(shù), 表示開始日期;
- end_date, 必選參數(shù), 表示結(jié)束日期;
- weekend, 可選參數(shù), 表示一周中哪些天是周末, 哪些天是工作日. 使用一個(gè)由0,1組成的字符串表示, 0表示工作日, 1表示周末, 如每周雙休可以表示為: "0000011", 周日單休可以表示為: "0000001".
- holidays, 可選參數(shù), 表示節(jié)假日, 可以自定義節(jié)假日.
根據(jù)NETWORKDAYS.INTL函數(shù)的定義我們可以得到此種情況的解決方案, 需要使用前三個(gè)參數(shù). 示例如圖所示:
其中前兩個(gè)參數(shù)A2和B2分別表示開始和結(jié)束日期, 第三個(gè)參數(shù)"0000001"表示每周周日是休息日, 其他是工作日. 工作日天數(shù)的計(jì)算公式就是: =NETWORKDAYS.INTL(A2,B2,"0000001")
4 雙休或單休, 考慮法定節(jié)假日和調(diào)休
這種情況是最復(fù)雜的情況, 也是最常見的情況. 如果只考慮節(jié)假日, 那么我們只需要使用NETWORKDAYS函數(shù)就可以解決. 但是涉及調(diào)休之后不行了.
所以還是需要使用NETWORKDAYS.INTL函數(shù), 但是需要換個(gè)思路來解決問題.
- 首先, 把一周的7天都作為正常的工作日;
- 然后, 所有的周末和法定節(jié)假日, 減去所有的調(diào)休作為節(jié)假日;
- 最后, 計(jì)算一段時(shí)間內(nèi)的所有天數(shù)減去節(jié)假日的天數(shù)就是正常的工作日天數(shù).
按照這個(gè)思路, 函數(shù)的前兩個(gè)參數(shù)仍然是開始日期和結(jié)束日期, 第三個(gè)參數(shù)定義為"0000000", 第四個(gè)參數(shù)是所有的周末加所有的節(jié)假日, 減去調(diào)休的日期.
那么下圖的例子中, 工作日天數(shù)的計(jì)算公式就是: =NETWORKDAYS.INTL(A2,B2,"0000000",$E$2:$E$116)
5 函數(shù)NETWORKDAYS詳細(xì)使用方法
該函數(shù)用于計(jì)算從指定的開始日期(包含)到結(jié)束日期(包含)的工作日的天數(shù), 工作日不包括周末和指定的節(jié)假日. 具體的使用方法如下, 詳細(xì)使用方法參見官方文檔
語(yǔ)法
NETWORKDAYS(start_date, end_date, [holidays])
該函數(shù)有三個(gè)參數(shù), 每個(gè)參數(shù)代表的意義如下:
- Start_date, 必需參數(shù)作儿。 一個(gè)代表開始日期的日期。
- End_date, 必需參數(shù)馋劈。 一個(gè)代表終止日期的日期攻锰。
- Holidays, 可選參數(shù)晾嘶。不在工作日歷中的一個(gè)或多個(gè)日期所構(gòu)成的可選區(qū)域，例如：省/市/自治區(qū)和國(guó)家/地區(qū)的法定假日以及其他非法定假日娶吞。該列表可以是包含日期的單元格區(qū)域垒迂，或是表示日期的序列號(hào)的數(shù)組常量。
函數(shù)NETWORKDAYS.INTL的詳細(xì)使用方法
返回兩個(gè)日期之間的所有工作日數(shù)妒蛇，可以使用參數(shù)指定哪天是周末(這對(duì)于單休的公司非常有用)机断，也可以指定除周末外的其他節(jié)假日。周末和任何指定為假期的日期不被視為工作日绣夺。詳細(xì)使用方法可以參考官方文檔
語(yǔ)法
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
該函數(shù)有三個(gè)參數(shù), 每個(gè)參數(shù)的解釋如下:
- **start_date 和 end_date **, 必需參數(shù)吏奸。 要計(jì)算其差值的日期。 start_date 可以早于或晚于 end_date乐导，也可以與它相同苦丁。
- weekend 可選。 用于指定一個(gè)星期中哪些天是工作日和哪些天是周末, 有兩種表示方法, 一種是使用數(shù)字來表示weekend, 另一種是使用字符串來表是
可以用下表中的數(shù)字來表示, 第一列是數(shù)字, 第二列是該數(shù)字代表的意義.
| 周末數(shù) | 周末日 |
|---|---|
| 1 或省略 | 星期六物臂、星期日 |
| 2 | 星期日旺拉、星期一 |
| 3 | 星期一、星期二 |
| 4 | 星期二棵磷、星期三 |
| 5 | 星期三蛾狗、星期四 |
| 6 | 星期四、星期五 |
| 7 | 星期五仪媒、星期六 |
| 11 | 僅星期日 |
| 12 | 僅星期一 |
| 13 | 僅星期二 |
| 14 | 僅星期三 |
| 15 | 僅星期四 |
| 16 | 僅星期五 |
| 17 | 僅星期六 |
字符串是長(zhǎng)度為7的0沉桌、1組合, 0表示工作日, 1表示周末, 如"0000011"表示周一到周五是工作日, 周六和周日是周末; "0000001"表示周一到周六是工作日, 周日是休息日.
- holidays 可選。 一組可選的日期算吩，表示要從工作日日歷中排除的一個(gè)或多個(gè)日期留凭。 holidays 應(yīng)是一個(gè)包含相關(guān)日期的單元格區(qū)域，或者是一個(gè)由表示這些日期的序列值構(gòu)成的數(shù)組常量偎巢。 holidays 中的日期或序列值的順序可以是任意的蔼夜。
