Excel。土日祝日を除いた前日・後日を算出する方法。2019年の大連休対策
＜WORKDAY関数＞
2019年は平成31年から新元号へ変わったりしますが、事務職や経理さんが頭をかかえるのが、4月29日からの大連休にともなう、振込日。前日なのか後日なのか？2019年は、大連休以外にも振替休日が発生したりしますので、改めて日付を確認する方法を押さえておきましょう。
【祝日一覧を作成する】
最初に用意しないといけないのは、祝日一覧表。
2019年は、12月に祝日がないんですね。
月初・月末と25日あたりに振込日があることが多いので、今回は、4月30日に振込予定日がある場合を例として日付を求めていくことにしましょう。
なお、この表のA列ですが、表示形式のユーザー定義を使って、yyyy/m/d(aaa)と設定しているので、日付の後ろに曜日が表示しております。
※入力するのが面倒な場合は、コピーして直接Excelに貼り付けて、フラッシュフィルを使ったりして、一覧表を作ってみてください。
日付 祝日名
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/11/3 文化の日
2019/11/4 振替休日
【WORKDAY関数を使って振込日を算出しましょう】
次のような表を用意します。
今回はわかりやすいようにするために、2月3月4月で見てみましょう。
土曜日曜と祝日を除くには、WORKDAY関数を使うことで求めることができますが、ちょっと考える必要があります。
ではF2をクリックして、WORKDAY関数ダイアログボックスを表示しましょう。
開始日は、E2+1と入力します。
日数は、-1と入力します。
祭日は、$A$2:$A$21。オートフィルで数式をコピーしますので、絶対参照を設定しています。
F2の数式は、
=WORKDAY(E2+1,-1,$A$2:$A$21)
では、オートフィルで数式をコピーしましょう。
4月末日は、繰り上がって繰り上がって、4月26日になっちゃうんですね。ほとんど、25日と変わらんでしょう！こりゃ～各担当者さん。業務が立て込んじゃうよね。
ところで、なんで、+1したり、-1したりしているのでしょうか？
関数の動きを確認しておきましょう。
2月28日に+1すると3月1日になってしまいますが、日数で-1（マイナス1）しますので、2月28日になります。
この2月28日は、A2:A21のデータと該当しないので、2月28日と算出されます。
3月31日は+1すると、4月1日月曜日になるのですが、日数で-1すると、3月31日日曜日になり、WORKDAY関数は、土日を除外するので、4月1日月曜日の土日を除いた-1。
すなわち3月29日金曜日を算出します。
ということで、4月30日に+1すると5月1日になるのですが、5月1日は、『即位の礼』でA2:A21にデータが該当するために、土日祝日を除いて-1した、4月26日金曜日と算出されるわけですね。
では、土日祝日を除いた翌日だった、どのような計算式を作ればいいのでしょうか？
G2をクリックして、WORKDAY関数ダイアログボックスを表示しましょう。
開始日は、E2-1
日数は1
祭日は$A$2:$A$21
G2の数式は、
=WORKDAY(E2-1,1,$A$2:$A$21)
それでは、オートフィルで数式をコピーして確認してみましょう。
3月31日で動きを確認してみましょう。
3月31日日曜日の-1なので、3月30日土曜日ですが、土日を除いた日数+1なので、4月1日月曜日と算出されました。
4月30日火曜日は、祝日なので、-1しても、4月29日月曜日。A2:A21のデータと該当するので、日数+1するのですが、5月1日【即位の礼】から5月6日月曜日の「こどもの日の振替休日」まで祝日のデータと合致してしまうので、5月7日火曜日が算出されています。
ということで、今年2019年は、カレンダーをきちんと確認しておかないと、銀行窓口やATM激混なんてことが多発しかねませんので、注意が必要なのかもしれませんね。
