01.Excel編

Excelでn営業日前・n営業日後の簡単な表示方法(祝日対応)

Excelで「10営業日前の日付を表示したい」「10営業日後の日付を表示したい」など、営業日を数えて表示したいとお考えかと思います。

また、営業日に祝日を含まないようにできない「なぁ。とあわせてお考え方と思います。

Excelは非常に便利なツールですが、なかなか思った情報を計算する関数と出会うのは難しいのかもしれません。

 

請求書の納品日や、商品の発送日、今月の締め日までの残日数などの計算に活用することができます。

まずは関数の考え方をご紹介していき、5章では実戦で利用している内容をご紹介していきます。

 

今回はExcelの数ある関数の中で「WORKDAY関数」を利用して、10営業日前や10営業日後などの営業日をカウントして該当する日付を表示する方法についてご紹介していきます。

 

1. n営業日前・n営業日後を数えるのは「WORKDAY関数」

10営業日後の日付が知りたい、期日として10営業日後を自動で表示するようにしたいなど、休日を含む日数ではなく営業日を数えてほしいことが多々ありますよね。

日付を足したり引いたりすることは簡単にできますが、営業日についてはどうしたらよいかは、あまり知っている方もいないかと思います。

 

「WORKDAY関数」は営業日をカウントしてくれる関数で、次のようなルールがあります。

同時に10営業日前と10営業日後を表示する式も見てみましょう。

祝日を考えると複雑になりますので、祝日に関しては4章でご紹介をしますね。

 

【式の概要】

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

という式で営業日を計算していきます。

具体的な例はこちらの図で確認しましょう。

 

ポイント

・「WORKDAY関数」は土日休みで利用

・「WORKDAY関数」は祝日休みも追加可能

・上記以外は「WORKDAY.INTL関数」を利用(6章)

ポイント

・本日を自動で「今日の日付」にしたい場合は「=TODAY()」を利用

 

2. n営業日前を表示する方法(納期から逆算など)

1章でご紹介した「WORKDAY関数」の式の概要のうち(   )の「日数」に負の数(-1・-2・-3・・・)を入れると、1営業日前、2営業日前、3営業日前と「n営業日前」を表示することができます。

 

実践では「n営業日後」を自動で入力するケースの方が多いかと思いますが、「n営業日前」の日付を知ることができるのは便利ですね。

私の場合には、株式投資で「レラティブストレングス」についてのデータを取得していますので、n営業日前の日付を計算してくれることはとてもありがたく利用しています。

ECサイトの運営や配送業などの場合には、納期から●日前には最低限発送する必要があるなどの時に利用しますね。

 

【式の概要】

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

 

ポイント

・営業日前は「日数」の欄に負の数(マイナス)を入れる

・10営業日前なら「-10」とする

・祝日を加味したい場合には4章を参照する

 

3. n営業日後を表示する方法(本日から計算など)

1章でご紹介した「WORKDAY関数」の式の概要のうち(   )の「日数」に正の数(1・2・3・・・)を入れると、1営業日後、2営業日後、3営業日後と「n営業日後」を表示することができます。

 

請求書や納期などを計算するときなど営業日を自動で計算したいケースは多々ありますよね。

今日の日付からn営業日後を計算する場合ケースとしては、請求書や納品書など受注した日付から先方へ伝える納期を自動計算してくれるのは便利ですよね。

私の場合には、請求書の支払期日をこちらを使って自動的に表示されるようにしています。

 

【式の概要】

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

 

ポイント

・営業日前は「日数」の欄に正の数を入れる

・10営業日後なら「10」とする

・祝日を加味したい場合には4章を参照する

 

4. 祝日・特別休日を加味する場合には「祝日リスト」が必要

2・3章で「WORKDAY関数」を使って「n営業日前」「n営業日後」の日付を計算する方法をご紹介しましたね。(土日休みが前提)

続いては、祝日を加味する場合です。

 

祝日というのは当たり前ですが日本だけのルールになります。毎年曜日も異なりますし、状況によって振替祝日もありますよね。

よって、祝日については自動では設定できないため、ご自身で祝日一覧を作成する必要があります。

祝日シートを作る、または空きスペースに入力するなどして一覧を作成し、その一覧を式で指定する必要があります。

 

インターネットで「祝日 2021年」などと検索すると多くのサイトが出てきます。

4-2.に私のリストを掲載していますので、ぜひ参考にしてください。

 

4-1. 祝日を加味する営業日の計算

「WORKDAY関数」で祝日を加味せず土日を除く営業日の計算は2章・3章のとおりですが、祝日を加味する場合には [祝日] の部分に祝日とすべき日付一覧を指定します。

4-2.でご紹介する祝日リストを使って、ご自身のExcelに作成していきましょう。

ただし、リストの作り方と範囲指定に特徴があるため、4-2で確認しておきましょう。

 

Excelの場合、シートを分けた方が今後のカスタマイズ等で安定してきますね。

式が苦手な方は同じシートに作りたいと思うかもしれないですが「(シート名)!(範囲)」というシンプルな指定で別シートを取り込めます。

下の例であれば「祝日!A2:A23」とするだけです。同じシートだと「祝日!」を入れないだけですね。

 

ちなみに、Excelの複数のファイルで営業日を換算する場合には手入れが大変ですので、Googleのスプレッドシートの方がメンテナンスは楽ですね。(IMPORTRANG関数を想定)

 

【式の概要】

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

 

4-2. すぐ使える祝日リスト

次のリストは私の作成しているリストから2020年~2022年の祝日リストを記載しました。

祝日が土日の場合には本来は記載不要ですが、毎年祝日の種類を調べるのは大変なためあえて入力しています。

 

また、この祝日リストには会社の年末年始などの休日を入れておくと、その日も祝日と同様にカウントから外してくれます。

私の場合には、株式投資で利用していますので、赤枠のように「年末年始の相場がお休みの日」は入力しています。

 

日付曜日名称日付曜日名称日付曜日名称
2020/1/1元日2021/1/1元日2022/1/1元日
2020/1/2年末年始2021/1/2年末年始2022/1/2年末年始
2020/1/3年末年始2021/1/3年末年始2022/1/3年末年始
2020/1/13成人の日2021/1/11成人の日2022/1/10成人の日
2020/2/11建国記念の日2021/2/11建国記念の日2022/2/11建国記念の日
2020/2/23天皇誕生日2021/2/23天皇誕生日2022/2/23天皇誕生日
2020/2/24振替休日2021/3/20春分の日2022/3/21春分の日
2020/3/20春分の日2021/4/29昭和の日2022/4/29昭和の日
2020/4/29昭和の日2021/5/3憲法記念日2022/5/3憲法記念日
2020/5/3憲法記念日2021/5/4みどりの日2022/5/4みどりの日
2020/5/4みどりの日2021/5/5こどもの日2022/5/5こどもの日
2020/5/5こどもの日2021/7/22海の日2022/7/18海の日
2020/5/6振替休日2021/7/23スポーツの日2022/8/11山の日
2020/7/23海の日2021/8/8山の日2022/9/19敬老の日
2020/7/24スポーツの日2021/8/9休日2022/9/23秋分の日
2020/8/10山の日2021/9/20敬老の日2022/10/10スポーツの日
2020/9/21敬老の日2021/9/23秋分の日2022/11/3文化の日
2020/9/22秋分の日2021/11/3文化の日2022/11/23勤労感謝の日
2020/11/3文化の日2021/11/23勤労感謝の日2022/12/30年末年始
2020/11/23勤労感謝の日2021/12/30年末年始2022/12/31年末年始
2020/12/30年末年始2021/12/31年末年始
2020/12/31年末年始

 

祝日リストを作成する際の注意点です。範囲指定に癖がありますので、次のいずれかの方法を取ります。

私は①でやる方がメンテナンス性も良く、おすすめです。

 

①複数年にわたる場合にも、一列で祝日を記載する。A列だけを [祝日] の範囲とする

 

②1年1列にするが、日付が連続している必要があるため、祝日の日付だけを記載する

 

5.  土日休みではない場合は「WORKDAY.INTL関数」を利用

「WORKDAY関数」は土日を休日とする関数で、祝日を加味した計算ができることをご紹介してきました。

 

「土日休みじゃない場合にはどうしたらいいの?」という場合も多いと思います。

そんな場合には「WORKDAY.INTL関数」を使います。

 

ここではあまり詳しくご紹介しませんが、式の概要はこちらになります。

基本的には「WORKDAY関数」と同じですが、「週末」を指定できるようになります。

 

こちらの例の場合は週末を「3」にしたので、「月曜と火曜」を週末の営業日外としたことになります。

 

【式の概要】

= WORKDAY ( 開始日 , 日数 , 週末 , [祝日] )

 

5-1.  週末の指定方法

「WORKDAY.INTL関数」で土日以外の週末を指定する方法は、次のとおり17種類あります。

「WORKDAY.INTL関数」は2連続休日の週末または、1日の週末の場合に利用することができます。

 

もし休日が「月曜と木曜」であり、営業日を計算する場合には別の方法がありますが、複雑になるため別の機会にご紹介をしたいと思います。

 

週末に入力する値週末
1または省略土曜日と日曜日
2日曜日と月曜日
3月曜日と火曜日
4火曜日と水曜日
5水曜日と木曜日
6木曜日と金曜日
7金曜日と土曜日
11日曜日のみ
12月曜日のみ
13火曜日のみ
14水曜日のみ
15木曜日のみ
16金曜日のみ
17土曜日のみ

 

5-2.  月曜と火曜を休日とする場合の「WORKDAY.INTL関数」

月曜と火曜を休日とする場合ですが、週末を「3」と指定することで月曜と火曜を除いた営業日で計算することができます。

また、祝日は4章のとおり「WORKDAY関数」と同様に設定をすることで祝日もカウントしないカタチで計算をしてくれます。

 

 

さいごに

「n営業日前」「n営業日後」を知りたい場合の、計算方法について「WORKDAY関数」と「WORKDAY.INTL関数」の考え方はご理解をいただけたでしょうか。

 

基本的には土日休みを起点とするので「WORKDAY関数」を利用しますが、土日外がお休みの場合には「WORKDAY.INTL関数」を利用して営業日を計算していきます。

日付の計算は単純で楽なのですが、営業日の計算をどのようにしていくのかお悩みの方は非常に多いと思いますので。本記事を読んで営業日計算ができるようになった!のではないでしょうか。

 

こちらを少し応用すると月末の日付を計算したり、連続しない休日を数えない営業日計算ができるようになったりもします。

まずは、土日休み、連続2曜日、週1休みの場合の方法を試していただき、ネットで調べながら応用にチャレンジしていただければと思います。

  • この記事を書いた人
  • 最新記事

FPIT

▶「株式投資×IT活用」を考える兼業投資家
▶ ファイナンシャルプランナーを活かし学んだことをFP視点でアウトプットし、 個人ができるIT活用・効率化ツールの自作版も公開
▶ダイエットも低糖質・ファスティングの両方を成功し▲20kg
▶ KWは株式投資/IT/Excel/スプレッドシート/お金/その他ヒント
▶ ご相談やコメント等がございましたら、お問い合わせフォームからご連絡ください

-01.Excel編

© 2022 FPITブログ