Excel。振込日などで使う土日祝日を除いた前日・後日を求める方法
＜DATE関数+WORKDAY関数 EOMONTH関数＞
新年を迎えると日付関係で確認しておかないことが発生する、ビジネスマンも多いですよね。
例えば、月末締めの翌々10日払いとか振込日など関連する日があるとは思います。
この支払日や振込日が平日ならば問題はないのですが、
土日祝日に絡んだりすると、とても厄介なんですね。
土日祝日の前に振込しておかなければいけないのか？土日祝日の後でいいのか？
資金繰りに絡んできますので、しっかり把握しておきたいところ。
そこで、今回は、2018年の祝日を確認しつつ、土日祝日を除いた前日、
あるいは後日を求める方法をご紹介してきます。
まず、次の表を用意します。
A1には、2018と入力して、表示形式で単純に、G/標準"年"として、
2018年と表示できるようにしております。
A4:A9も同じように、G/標準"月"として、表示上、1月としてあります。
このあと、これらの数値をDATE関数で日付を求めるために使うので、
数値でないとマズイわけです。
月末の列ですが、
B4には、次の数式を設定してあります。
月末を算出する関数の、EOMONTH関数を使って、
それぞれの月末を算出しております。
開始日には、DATE($A$1,$A4,1)
DATE関数は、日付を作ることができる関数ですね。
このために、先ほど表示形式を使っていたわけですね。
月には、0（ゼロ）。今月末を知りたいので、0（ゼロ）ですね。
あとは、OKボタンをクリックして、オートフィルを使って数式をコピーしましょう。
B4の数式は、
=EOMONTH(DATE($A$1,$A4,1),0)
すると、このようになりましたね。
日付ではなくて、シリアル値で算出されてきましたので、
これも表示形式を使って日付にしましょう。
また今回は、曜日が絡みますので、曜日も表示できるように変更しましょう。
表示形式を、ユーザー定義を使って、yyyy/m/d(aaa)としてみましょう。
曜日月の日付が表示されましたね。
それでは、早速…といきたいところですが、
もっとも大切なデータを事前に作っておく必要があります。
次の表を確認してみましょう。
祝日の一覧表が必要になるのです。
これがないと、Excelが祝日かどうかの判断ができないのです。
作るのは大変だと思いますので、次の行をコピーして使ってください。
祝日 内容
2018/1/1(月) 元旦
2018/1/8(月) 成人の日
2018/2/11(日) 建国記念の日
2018/2/12(月) 建国記念の日の振り替え
2018/3/21(水) 春分の日
2018/4/29(日) 昭和の日
2018/4/30(月) 昭和の日の振り替え
2018/5/3(木) 憲法記念日
2018/5/4(金) みどりの日
2018/5/5(土) こどもの日
2018/7/16(月) 海の日
2018/8/11(土) 山の日
2018/9/17(月) 敬老の日
2018/9/23(日) 秋分の日
2018/9/24(月) 秋分の日の振り替え
2018/10/8(月) 体育の日
2018/11/3(土) 文化の日
2018/11/23(金) 勤労感謝の日
2018/12/23(日) 天皇誕生日
2018/12/24(月) 天皇誕生日の振り替え
先に結果を見てみましょう。
月末の前日か後日かを算出してみるとこのようになります。
4月30日は昭和の日の振り替え休日なので、
祝日前・祝日後ともに日が変わっていますよね。
このように、土日祝日を除くことができる関数。
それが、【WORKDAY関数】です。
WORKDAY関数を使って、C4の数式を作っていきましょう。
C4をクリックして、WORKDAY関数ダイアログボックスを表示しましょう。
開始日は、B4+1
日数には、-1
祭日は、$F$4:$F$23
あとは、OKボタンをクリックしてオートフィルで数式をコピーします。
なぜ、開始日で+1して、日数で-1するのかというと、
このWORKDAY関数なのですが日数の引数を0（ゼロ）にすることができないのです。
確認してみるとわかるのですが、
3月31日が土曜日なので、C6の数式を開始日に+1しないで、
日数を0にしてみると、3月31日が算出されてしまうのです。
つまり、日数が0だと土日でも、リアクションしないのです。
なので、+1して-1するという方法を使っています。
それでは、C4の数式は、
=WORKDAY(B4+1,-1,$F$4:$F$23)
同じように、D4の数式を確認しておきましょう。
=WORKDAY(B4-1,1,$F$4:$F$23)
こちらは、-1してから、+1するようにしております。
このように、WORKDAY関数を使うと、
土日祝日を除けることができますが、まずは、祝日一覧表を作ってからですね。
