業務では休日を除外して営業している日付や日数計算をしたい事があります。
「受注日の2営業日後を出荷日として表示」とか「毎月第5営業日を赤で表示」とか、「月の最終営業日にレポート作成」など、いろいろあるでしょう。
そんな営業日を計算するとき、会社独自の休日がからむと急に面倒くさくなりませんか?
まぁ文句を言っても仕方がないので、Excelで営業日を計算する関数の使い方を紹介します。
ExcelのWORKDAY関数でできること
Excelには稼働日を計算するための一連の関数、
WORKDAY関数や、NETWORKDAYS関数などが用意されています。
こいつらを使えばたいていの事はなんとかなるでしょう。
- 〇営業日後・〇営業日前の日付を計算したい・・・WORKDAY関数
- 〇営業日後・〇営業日前の日付を計算したい、土日が休み(=週末)じゃない場合、週末も含め休日を自分で指定できる関数・・・WORKDAY.INTL関数
- 2つの日付間の営業日数を計算したい・・・NETWORKDAYS関数
- 2つの日付間の営業日数を計算したい、土日が休み(=週末)じゃない場合、
週末も含め休日を自分で指定できる関数・・・NETWORKDAYS.INTL関数
では使い方を見ていきましょう。
土日と祝祭日を除外した営業日を計算する「WORKDAY関数」
開始日から起算して指定された日数だけ前、または後ろの日付を返すのが、WORKDAY関数です。
土日がデフォルトで週末判定されて、祝祭日をあらかじめセルに入力ておくことで、それらが営業日のカウントから除外されます。
書式
WORKDAY関数の引数には次の内容を指定します。
「開始日」:起算日を指定します。日付が入力されているセルを指定するか、日付を直接入力します。指定は必須です。
「日数」:起算日にプラス、またはマイナスしたい数(日数)を指定します。日数が入ったセルを指定しても、数字を直接入力してもOK。指定必須です。
「休日」:営業日数の計算から除外したい祝祭日をセルの範囲か名前で指定します。
これは省略可能で、省略した場合は土日だけがデフォルトで除外されます。
ちなみに関数入力中に表示されるポップヒントでは、[祭日]となっていますが[休日]と同じ意味です。
戻り値はシリアル値なので、ぱっと見 意味不明な数字が返って来ますが、日付形式に書式設定してやれば大丈夫。
独自の週末と祝祭日を除外して営業日を計算する「WORKDAY.INTL関数」
開始日から起算してn日前やn日後の日付を返すのはWORKDAY関数と同じですが、
土日が休み(=週末)ではない場合でも、週末を独自に指定することができるのが、WORKDAY.INTL関数です。
書式
「開始日」:起算日を指定。日付が入力されているセル指定か日付を入力します。指定は必須。
「日数」:起算日にプラス、またはマイナスしたい数(日数)を指定。日数が入ったセルを指定しても、数字を直接入力してもOK。指定必須。
「週末」:週末を示す週末番号、または文字列を指定します。
「休日」:営業日数の計算から除外したい祝祭日をセルの範囲か名前で指定。
引数「開始日」と「日数」と「休日」については、WORKDAY関数と同じ内容で、
違うのは第3引数の「週末」です。
土日は休みじゃないんだけど、他の曜日が休みですっていう場合、それを「週末」として独自に定義することができます。
「週末」は省略可能で、省略した場合は土日が週末として指定されたことになります。
「週末」の指定方法はちょっと独特で、週末を示す「週末番号」または文字列で指定します。
週末番号は以下の数字です。
週末番号 | 意味 |
---|---|
1 (又は省略) | 土・日 |
2 | 日・月 |
3 | 月・火 |
4 | 火・水 |
5 | 水・木 |
6 | 木・金 |
7 | 金・土 |
11 | 日のみ |
12 | 月のみ |
13 | 火のみ |
14 | 水のみ |
15 | 木のみ |
16 | 金のみ |
17 | 土のみ |
週に1日 or 連続した2日、決まった曜日が休みの場合、この週末番号を指定します。
上記のパターンに該当しない場合の指定方法は、0と1のフラグで指定します。
0は営業日、1は休みという意味で、半角の0か1を月火水木金土日の順に並べていきます。
例えば日・火・金が休みなら、"0100101"という指定になります。
特定の曜日が休みではないなら[週末]には"0000000"と指定しておいて、[休日]で指定してやればOK。
ちなみに"1111111"と指定することはできません。
WORKDAY関数で最終営業日を算出する例
その月の最終営業日を算出したい場合、翌月1日の1営業日前を求めます。
日曜が休みの場合、WORKDAY.INTL関数で週末番号11を指定してこのように。
上の図は、2016年4月の最終営業日を算出しています。
起算日が同じでも、WORKDAY関数とWORKDAY.INTL関数とで結果が異なる例です。
2つの日付間の営業日数を計算する「NETWORKDAYS関数」
一方、NETWORKDAYS関数は、2つの日付間の稼働日数を計算します。
土日が休みで、[休日]の指定はセルに入力しておくという仕様は、WORKDAY関数と同じです。
書式
「開始日」:日付FROM(起算日)を指定します。日付が入力されているセルを指定しても、日付を直接入力してもOK。指定は必須です。
「終了日」:日付TO(期間の最終日)を指定します。開始日同様、日付が入ったセルを指定しても、日付を直接入力してもOK。これも指定必須です。
「休日」:営業日数の計算から除外したい祭日をセルの範囲か名前で指定します。
これは省略可能で、省略した場合は土日だけ除外されます。
WORKDAY関数と同じ理屈。
独自の週末と祝祭日を除外して2つの日付間の営業日数を計算する「NETWORKDAYS.INTL関数」
NETWORKDAYS.INTL関数は、週末を自分で指定した上で2つの日付間の日数を計算する関数です。
書式
「開始日」「終了日」「休日」はNETWORKDAYS関数と同じで、
第3引数の「週末」はWORKDAY.INTL関数と同じ理屈です。
...だんだん説明が雑になってきました。
4つの関数についての詳細は、本家Microsoftのサポートページもどうぞ。(雑)
WORKDAY.INTL 関数 - Office のサポート
NETWORKDAYS.INTL 関数 - Office のサポート
休日を入力するのは面倒だな...
WORKDAY関数群は休日セル範囲ありきの方法なので
ExcelのWORKDAY関数は視覚的にとても解りやすくて良いのですが、休日をセルに入力しておかなくてはならず、入力した休日の上限・下限を超えた日付の計算は正しい結果が得られません。
たとえば今年の分だけ休日を入力した状態で、来年の営業日を計算しようとしても正しい営業日計算はされず、土日だけ除外されます。
正しくないのはマズい。なにより面倒くさいのはイヤ。
そんな貴方におすすめの関数があります。
井上さんの関数ならカレンダーシートの手入力は不要です
その関数は「Excelでお仕事!」というサイトで紹介されています。
こちらのサイト、ご存知の方も多いでしょう。
Excelの基本操作からVBA、API、セキュリティまで解説されていて、私も実務でいつも参考にさせていただいているサイトです。
こちらのサイトに掲載されている関数なら、あらかじめシートに休日を入力しておかなくても祝日対応が可能です。
ハッピーマンデーももちろん対応。
日付が固定されていない春分の日、秋分の日だって自動計算してくれますし、クラス(clsAboutCalendar2R.cls)を変更すれば、会社独自の休日をカスタマイズすることもできます。
2019年は祝日が変則的ですが、すでに対応されているのも心強いです。
ここで紹介されている公開プロシージャはVBAから呼び出すことも、ワークシート関数として使うこともできます。
こちらの標準モジュール「modAboutCalendar2R」(カレンダー関連関数(クラス呼び出し部分))と、クラスモジュール「clsAboutCalendar2R」(カレンダー及び日付処理(祝日含む)関連関数クラス)をプロジェクトに実装して使用します。
詳しい使用方法は「Excelでお仕事!」の記事内でわかりやすく説明されているのでそちらをどうぞ。
年末年始や会社独自の休日のメンテナンス方法も、同記事内の「会社休日を祝日パラメータに追加する場合は?」で詳しく説明されています。
井上さんの関数の中の公開プロシージャは、Excel関数にそれぞれ該当しています。
「SUMEIGYOBI2」 ・・・日数経過後の「営業日」を算出します。
これは「WORKDAY関数」に該当するものです。
「SUMEIGYONISSU2」・・・起算日から終了日までの営業日数を計算します。
こちらは「NETWORKDAYS関数」に該当します。
この関数で「.INTL」みたいなことはできるのか?
井上さんの関数を使う上で、祝祭日が休みではない場合、祝日パラメータ(クラスモジュール)をメンテナンスする必要があります。
祝日が休みではない場合のカスタマイズ方法
クラス「clsAboutCalendar2R」の「GP_MakeHoliParameter」の中の該当する日付の「GP_ApendHoliParam」をコメントアウトまたは独自に追加します。
いやぁ、そこまでいじるなら、潔く休日シートを手入力で作成してもいいんだよ...
追記(2018/12)
井上さんの祝日判定ロジックが大幅リニューアルされています
わたし大絶賛の井上さんの祝日判定ロジックが2018年以降、大幅リニューアルされています。
祝日の定義を「祝日パラメータ」シートで設定しておいて、祝日が変更になったときもプログラムを修正するのではなく「祝日パラメータ」シート上の祝日を変更しちゃおうっていう方針だそうです。
その方が使いやすいという方とそうでない方に分かれそうですね。
祝日パラメータシートの作成は↓こちら
VBA応用(年間カレンダーの作成2)
祝日パラメータシートを使用した営業日数の算出は↓こちら
VBA応用(「営業日数」の算出)
一方で、「祝日パラメータ」をシートではなくソースコードで記述するサンプルは
↓こちら
VBA応用(年間カレンダー作成(祝日パラメータシートを使わない方法))
従来のモジュール使ってるんですって方、「以前の方法のサンプル」としてこちらに残してくださっています。
VBA応用(年間カレンダーの作成)
いやぁ有難い事です。
さいごに
いかがでしたでしょうか。
自力ではなく完全に他力本願でしたが、Excel関数を使うもよし、井上さんの素晴らしい関数を使うもよし、楽チンに正確に日付を計算する方法の紹介でした。
それでは!