12/30/2017

Excel。振込日などで使う土日祝日を除いた前日・後日を求める方法【WORKDAY】

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関数を使うと、
土日祝日を除けることができますが、まずは、祝日一覧表を作ってからですね。