Excel。手早く20日締め翌月末払いの日程表をつくりたい
＜DATE・EDATE・DAY・YEAE・MONTH関数＞
Excelの日付計算は、頭の中で考えるように、スムーズにいかないところがあります。
たとえば、次のような、20日締め翌月末払いの日程表をつくるとします。
A列に仕入日を入力すると、締め日と支払日が表示されるように数式を設定するわけですが、手早く作りたいわけです。
B列の締め日は、20日締めなので、20日より前ならば、同月20日ですが、21日以降ならば、翌月20日で表示したいわけです。
なお、今回は、土日祝日は考慮しておりません。
さて、20日より前とか後となれば、IF関数をつかって算出させればいいように思います。
B4にIF関数をつかった数式を設定するとすれば、
=IF(DAY(A4)<=20,DATE(YEAR(A4),MONTH(A4),20),DATE(YEAR(A4),MONTH(A4)+1,20))
確かに間違いではないのですが、当然長くなります。
わかりやすい反面、可読性が悪化します。
そこで、B4に次のような数式でも算出することができます。
=DATE(YEAR(A4),MONTH(EDATE(A4-20,1)),20)
数式がコンパクトになったことで、可読性が向上していますね。
それに、IF関数をつかわないで、算出しています。
締め日の着目点は、「20日前なのか後なのか」ということです。
そこで、MONTH関数の引数を確認します。
MONTH(EDATE(A4-20,1))
MONTH関数は、月を算出する関数です。
EDATEは、月ちがいの同日を算出する関数です。
仕入日から20減算した日付をつかい、EDATE関数で算出された月に「+1」した数値を月として、算出しています。
ケースとして、2022/4/21ならば、20日減算すると2022/4/1なので、月は、4+1で5と算出されます。
2022/5/20が締め日となり、2022/4/20ならば、20日減算すると、2022/3/31なので、月は、3+1で4と算出されます。
よって、2022/4/20が締め日と算出することができるというわけです。
このようなことから、IF関数をつかわないで、算出することができるというわけです。
支払日の数式は、IF関数で算出しても、締め日と同じ方法で算出しても、可動性に変わりは、あまりありません。
C4の数式は、
IF関数ならば、
=IF(DAY(A4)<=20,EOMONTH(A4,1),EOMONTH(A4,2))
EDATE関数ならば、
=DATE(YEAR(A4),MONTH(EDATE(A4-20,3)),1)-1
関数の説明をします。
=IF(DAY(A4)<=20,EOMONTH(A4,1),EOMONTH(A4,2))
この数式で使用している、EOMONTH関数は、月末日を算出する関数です。
20日以前ならば、一か月後の月末を算出し、それ以外は、二か月後の月末を算出することができます。
=DATE(YEAR(A4),MONTH(EDATE(A4-20,3)),1)-1
MONTH関数は、先程と同じで、20日減算した日の、同日の3か月後の月を算出しています。
2022/4/21のケースだと、20日減算するので、2022/4/1で、この同日の3か月後ですから、MONTH関数で算出された月は、「7」。
DATE関数で算出されるのが、2022/7/1。
この日から「-1」することで、前月末日。
すなわち、翌月末日を算出することができるので、2022/6/30と算出することができるわけです。
このように、Excelには、様々な日付の関数が用意されいるので、色々組み合わせてみると数式を改善することができるかもしれませんね。
