Excel。10日締め翌10日払いの日が、土日祝日なら前営業日にするにはどうするの
＜WORKDAY＋EOMONTH＋IF＋DAY関数＞
支払予定日が、土日祝日だと、だいたい、前営業日に支払いをすることになるわけです。
その前営業日を算出するには、どのように関数を組み合わせたたらいいのでしょうか。
次のケースをつかって説明します。
結論というか、対応した数式をまず、紹介すると、
=WORKDAY(EOMONTH(A6,IF(DAY(A6)<=10,0,1))+11,-1,$E$2:$E$3)
という数式をつくることで、土日祝日ならば、前営業日を算出することができます。
A2の2023/10/3は、10日締めなので、翌月の10日が支払日です。
B2のように2023/11/10が支払日となります。
B2に設定した数式は、
=EOMONTH(A2,IF(DAY(A2)<=10,0,1))+10
この式がベースとなっていきます。
EOMONTH関数は、月末日を算出する関数です。
1番目の引数は、「開始日」なので、A2を設定します。
これで、2023/10/31が月末日です。
2番目の引数は、「月」です。
10日締めなので、当月なのか、翌月の月末なのかという月を算出させる必要があります。
IF(DAY(A6)<=10,0,1))
IF関数とDAY関数をつかって、10日以前なのどうかを判断させています。
10日以前ならば「0」。
そうでなければ「1」とすることで、当月末なのか、翌月末なのかを算出できます。
その日付に「+10」すれば、10日払いの日付を算出することができるというわけです。
もし、25日払いならば、「+25」とすればいいわけです。
だから、EDATE関数ではなくて、EOMONTH関数をつかったというわけです。
さて、算出した日付が、平日ならばいいのですが、土日祝日だった場合、金融機関がお休みなので、前営業日にしたいわけです。
そこで、土日祝日を除くことができるWORKDAY関数をつかって、先程のEOMONTH関数をネストします。
=WORKDAY(EOMONTH(A6,IF(DAY(A6)<=10,0,1))+11,-1,$E$2:$E$3)
WORKDAY関数の最初の引数は、「開始日」です。
これは、先程のEOMONTH関数で算出した数式を設定します。
ただし、先程、10日払いだから「+10」としましたが、前営業日にしたいので、ワザと1日多い「+11」にします。
2つ目の引数は、「日数」なので、これを「-1」と設定することで、前営業日を算出することができます。
この「+1」と「-1」の考え方ですが、前営業日にしたいので「-1」したいわけです。
土曜日の場合は、「-1」すれば金曜日なので、問題はないのですが、金曜日など平日の場合は、その日でいいのにもかかわらず、「-1」されてしまい、前日が支払日として算出されます。
そのため、わざと「+11」と一日多くして、「-1」させるという方法をつかっております。
最後3つ目の引数は、祝日のカレンダーを用意しておき、その日付にも対応するようにしています。
土日祝日が絡んだ場合、前営業日を算出する方法をご紹介しました。
