NETWORKDAYS関数
あるインシデントの受付～完了までの時間をカウントしたいというニーズがあり、
単純に完了日時-受付日時してしまうと、土日祝日まで時間に含まれるので、対応時間が実態に合わなくなる
ということで稼働日をカウントする関数を調べてみました
EXCEL:NETWORKDAYS関数
=NETWORKDAYS(開始日,終了日[,祝日])
日数がこれでカウントできるので、24時間を掛ければ経過時間が出せるようになります
祝日を設定するには、直接日付を入れてもいいが、祝日リストを作っておいて範囲指定するのが扱いやすい
| A | B | C |
|---|---|---|
| 2019/7/1 | 2019/7/31 | 2019/7/15 |
| 2019/7/16 |
=NETWORKDAYS(A1,B1,C1:C2)
とすると
20という結果が得られる
ちなみに、日付をカウントするDAYS関数だと=DAYS(完了日,開始日)と逆順になる
時間までカウントするには？
開始日の開始時間から終了日の終了時間までの営業時間をカウントするにはどうしたらいいか？
初日と最終日のところだけ数値を変換する
| A | B | C(祝日) |
|---|---|---|
| 2019/7/1 | 2019/7/31 | 2019/7/15 |
| 時間(始 | 時間(終 | 2019/7/16 |
| 10:00 | 18:00 |
=(初日の時間)+中間の日数*24時間+(最終日の時間)
=("24:00:00"-A3)+(NETWORKDAYS(A1,B1,C1:C2)-2)*"24:00:00"+(B3)
Excelの場合は[書式設定]>[ユーザー定義]で、[h]:mm:ssとすると、経過時間表記になります
GoogleSpreadSheetなら以下のようにも書けます
Sheetの場合は[表示形式]>[数字]>[経過時間]に書式を変更する
=time(24,0,0)-A3+(NETWORKDAYS(A1,B1,C1:C2)-2)*time(24,0,0)+(B3)
※この書式、Excelだとエラーになります。TIME関数の仕様で、Excelではtime(0,0,0)~time(23,59,59)までとなっているためのようです
営業時間でカウントしたい
ある案件が7/1 13:00 ~ 7/10 12:00 まで継続し、営業時間は10:00~19:00なので、営業時間で経過時間をカウントしたい場合
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 開始日 | 開始時間 | 完了日 | 完了時間 | 営業時間(始 | 終) |
| 7/1 | 13:00 | 7/10 | 12:00 | 10:00 | 19:00 |
=(初日の稼働時間)+(中間の稼働時間)+(完了日の稼働時間)
=(F2-B2)+(NETWORKDAYS(A1,B1,C1:C2)-2*9)+(D2-E2)
休日が土日とは限らない
NETWORKDAYS関数は土日を非稼働日として除きますが、必ずしも土日が休みとは限らないはずです
そんな場合にはNETWORKDAYS.INTLを使うことができます
NETWORKDAYS.INTL
=NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])
NETWORKDAYSに加えて、週末を引数で設定できます
| 週末番号 | 週末の曜日 |
|---|---|
| 1 または省略 | 土曜日と日曜日 |
| 2 | 日曜日と月曜日 |
| 3 | 月曜日と火曜日 |
| 4 | 火曜日と水曜日 |
| 5 | 水曜日と木曜日 |
| 6 | 木曜日と金曜日 |
| 7 | 金曜日と土曜日 |
| 11 | 日曜日のみ |
| 12 | 月曜日のみ |
| 13 | 火曜日のみ |
| 14 | 水曜日のみ |
| 15 | 木曜日のみ |
| 16 | 金曜日のみ |
| 17 | 土曜日のみ |
| うちは月・水が休みなんだけど、という場合は、7桁の数値で設定できます | |
| 0=稼働日 | |
| 1=非稼働日です | |
| 月水休みなら、 |
=NETWORKDAYS.INTL(開始日,終了日,"1010000"[,祝日])
のように書きます。最近流行の週4勤務などにも対応できそうですね
資料：2019年の祝日
2019/1/1
2019/1/14
2019/2/11
2019/3/21
2019/4/29
2019/4/30
2019/5/1
2019/5/2
2019/5/3
2019/5/4
2019/5/5
2019/5/6
2019/7/15
2019/8/11
2019/8/12
2019/9/16
2019/9/23
2019/10/14
2019/10/22
2019/11/3
2019/11/4
2019/11/23