きなこSHOW

どうやったら寝たまま仕事ができるかあれこれ考える。

Excelで営業日を計算する関数を紹介します

業務では休日を除外して営業してる日の日付や日数を計算したい事があります。
「受注日の2営業日後を出荷日として表示」とか、「毎月第5営業日を赤で表示」など、いろいろあるでしょう。
そんな営業日を計算するとき、年末年始や会社独自の休日がからむと急に面倒くさくなりませんか?

まぁ文句を言っても仕方がないので、ExcelAccessの両方で営業日を計算するサンプルを紹介したいと思います。まずはExcel篇からどうぞ!

ExcelのWORKDAY関数でできること

Excelには稼働日を計算するための一連の関数、
WORKDAY関数や、NETWORKDAYS関数などが用意されています。
こいつらを使えばたいていの事はなんとかなるでしょう。

  • 〇営業日後・〇営業日前の日付を計算したい・・・WORKDAY関数
  • 〇営業日後・〇営業日前の日付を計算したい、土日が休み(=週末)じゃない場合、週末も含め休日を自分で指定できる関数・・・WORKDAY.INTL関数
  • 2つの日付間の営業日数を計算したい・・・NETWORKDAYS関数
  • 2つの日付間の営業日数を計算したい、土日が休み(=週末)じゃない場合、
    週末も含め休日を自分で指定できる関数・・・NETWORKDAYS.INTL関数

では使い方を見ていきましょう。

土日と祝祭日を除外した営業日を計算する「WORKDAY関数」

開始日から起算して指定された日数だけ前、または後ろの日付を返すのが、WORKDAY関数です。
土日がデフォルトで週末判定されて、祝祭日をあらかじめセルに入力ておくことで、それらが営業日のカウントから除外されます。

書式

=WORKDAY(開始日, 日数, [休日])

WORKDAY関数の引数には次の内容を指定します。
「開始日」:起算日を指定します。日付が入力されているセルを指定するか、日付を直接入力します。指定は必須です。
「日数」:起算日にプラス、またはマイナスしたい数(日数)を指定します。日数が入ったセルを指定しても、数字を直接入力してもOK。指定必須です。
「休日」:営業日数の計算から除外したい祝祭日をセルの範囲か名前で指定します。
これは省略可能で、省略した場合は土日だけがデフォルトで除外されます。


ちなみに関数入力中に表示されるポップヒントでは、[祭日]となっていますが[休日]と同じ意味です。

戻り値はシリアル値なので、ぱっと見 意味不明な数字が返って来ますが、日付形式に書式設定してやれば大丈夫。

独自の週末と祝祭日を除外して営業日を計算する「WORKDAY.INTL関数」

開始日から起算してn日前やn日後の日付を返すのはWORKDAY関数と同じですが、
土日が休み(=週末)ではない場合でも、週末を独自に指定することができるのが、WORKDAY.INTL関数です。

書式

=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(翌月1日, -1, 休日)


日曜が休みの場合、WORKDAY.INTL関数で週末番号11を指定してこのように。

=WORKDAY.INTL(翌月1日, -1, 11, 休日)

 

上の図は、2016年4月の最終営業日を算出しています。
起算日が同じでも、WORKDAY関数とWORKDAY.INTL関数とで結果が異なる例です。

2つの日付間の営業日数を計算する「NETWORKDAYS関数」

一方、NETWORKDAYS関数は、2つの日付間の稼働日数を計算します。
土日が休みで、[休日]の指定はセルに入力しておくという仕様は、WORKDAY関数と同じです。

書式

=NETWORKDAYS(開始日, 終了日, [休日])

「開始日」:日付FROM(起算日)を指定します。日付が入力されているセルを指定しても、日付を直接入力してもOK。指定は必須です。
「終了日」:日付TO(期間の最終日)を指定します。開始日同様、日付が入ったセルを指定しても、日付を直接入力してもOK。これも指定必須です。
「休日」:営業日数の計算から除外したい祭日をセルの範囲か名前で指定します。
これは省略可能で、省略した場合は土日だけ除外されます。
WORKDAY関数と同じ理屈。

独自の週末と祝祭日を除外して2つの日付間の営業日数を計算する「NETWORKDAYS.INTL関数」

NETWORKDAYS.INTL関数は、週末を自分で指定した上で2つの日付間の日数を計算する関数です。

書式

=NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])

「開始日」「終了日」「休日」はNETWORKDAYS関数と同じで、
第3引数の「週末」はWORKDAY.INTL関数と同じ理屈です。

f:id:kinacco:20160922011014j:plain

...だんだん説明が雑になってきました。

4つの関数について、本家Microsoftのサポートページもどうぞ。(雑)

WORKDAY 関数 - Office のサポート

WORKDAY.INTL 関数 - Office のサポート

NETWORKDAYS 関数 - Office のサポート

NETWORKDAYS.INTL 関数 - Office のサポート

 

休日を入力するのは面倒だな...

WORKDAY関数群は休日範囲ありきの方法なので

ExcelのWORKDAY関数は視覚的にとても解りやすくて良いのですが、休日をセルに入力しておかなくてはならず、入力した休日の上限・下限を超えた日付の計算は正しい結果が得られません。

たとえば今年の分だけ休日を入力した状態で、来年の営業日を計算しようとしても正しい営業日計算はされず、土日だけ除外されます。
正しくないのはマズい。なにより面倒くさいのはイヤ。
そんな貴方におすすめの関数があります。

井上さんの関数なら休日の手入力は不要です

その関数は「Excelでお仕事!」というサイトで紹介されています。

VBA応用(「営業日数」の算出)

こちらのサイト、ご存知の方も多いでしょう。
Excelの基本操作からVBAAPI、セキュリティまで解説されていて、私も参考にさせていただいているサイトです。

こちらの関数が、本当にすごいんです。
会社独自の年末年始をカスタマイズできるほか、ハッピーマンデーはもちろん対応。
日付が固定されていない春分の日秋分の日だって自動計算してくれます。
プロシージャ内のコメントに「祝日法施行(1947年)以前,2151年以降(簡易計算不可)は無視」とありますので、1948~2150年の間の日付は大丈夫ですね。
2016年から追加された「山の日」もサイトを見たら追加済みで、つねにアップデートされているのも心強いです。

ここで紹介されている3本のマクロは、Publicプロシージャが異なるだけで、Privateプロシージャはすべて共通なので、紹介されている3本のマクロの各Publicプロシージャ、「SUMEIGYONISSU」「CHECKEIGYOBI」「SUMEIGYOBI」を1本のモジュールにまとめてしまっても良いかと思います。

これら3種の関数はVBAからも、ワークシート関数としても使えます。
使用方法は「Excelでお仕事!」の記事内でわかりやすく説明されています。
年末年始の休日のメンテナンス方法も、同記事内で説明されています。

井上さんの関数の中のPublicプロシージャ2本は、Excel関数にそれぞれ該当します。
「SUMEIGYONISSU」・・・起算日から終了日までの営業日数を計算します。
これは「NETWORKDAYS関数」に該当します。
「SUMEIGYOBI」 ・・・日数経過後の「営業日」を算出します。
こちらは「WORKDAY関数」に該当するものです。

この関数で「.INTL」みたいなことはできるのか?

井上さんの関数を使う上で、土日が週末ではない場合や、祝祭日が休みではない場合、この関数をメンテナンスする必要があります。

土日が週末ではない場合のカスタマイズ方法

「SUMEIGYONISSU」内の下記部分を変更します。

Excelでお仕事!VBA応用「営業日数」の算出より引用

intDay = 1:日、2:月、3:火、4:水、5:木、6:金、7:土ですので、ここを変えてやります。

上の図は火曜日と祝日がお休みの場合の例です。

祝日が休みではない場合のカスタマイズ方法

「GP_GetHoliday_Sub」の中の該当する祝日の部分をコメントアウトし、コメントにした分だけ、IXに加算する数を減らします。


Excelでお仕事!VBA応用「営業日数」の算出より引用


↑ 我が社は山の日は休みではない例。

いやぁ、ここまでいじるなら、潔く休日をセルに手入力してもいいんだよ...

 

いかがでしたでしょうか。
自力ではなく完全に他力本願でしたが、Excel関数を使うもよし、井上さんの素晴らしい関数を使うもよし、楽チンに正確に日付を計算する方法の紹介でした。

それでは!