仕入日から支払日を自動計算する方法
支払予定表を作成したい、という場合、
支払条件に従って、
仕入日から支払日を計算しないといけません。
支払条件から支払日を自動計算する
よくある締め条件として、次の３つを考えてみたいと思います。
- 月末締め 翌々月20日払い
- 20日締め 翌月15日払い
- 月末締め 翌月末日払い
「１．月末締め 翌々月20日払い」の計算
例えば、
- 2016年3月中の仕入は、2016年5月20日に支払う
- 2016年4月中の仕入は、2016年6月20日に支払う
というイメージですね。
結局、
仕入れた年・月の「翌々月20日」の日付を計算すれば良い
ということです。
このように、
「翌々月」、「20日」といった表現がある場合には、
元々の日付を、
year関数、month関数、day関数を使って
年・月・日を分解したうえで、
date関数を使うことで、
うまく日付計算ができる計算が多いです。
実際、
次のような感じで計算式化できます。
「２．20日締め 翌月15日払い」の計算
先ほどとは違い、「20日締め」ですから、
例えば、
2016年3月を例にとると、
| 1日～20日までの仕入 | →4月15日払い |
| 21日～31日までの仕入 | →5月15日払い |
というように、
同じ月であっても、仕入れた日により、
支払期日が変わってしまいます。
ですから、
仕入日に応じて「if関数」で、支払期日に変化をつけましょう。
先ほどの仕入日と支払日の関係を、
もう少し一般的に書き直すと、
| 1日～20日までの仕入 | →仕入れた年・月の翌月15日払い |
| 21日～月末までの仕入 | →仕入れた年・月の翌々月15日払い |
とも書けます。
ここまで書ければ、
if関数を使って場合分けをする以外は、
先ほどのパターンと同じように書けることがわかります。
結局、if関数とdate関数を組み合わせて、
次のような感じで計算式化できます。
「３．月末締め 翌月末払い」の計算
このパターンは、
「１．月末締め 翌々月20日払い」のパターンと似ているようで、
大きく異なるところがあります。
それは、
月末の日付が、月によって変わるところです。
ですから、
「１．月末締め 翌々月20日払い」と同じように書き方ができません。
要は、
下の「○○」の部分を簡単に埋められないのです。
ここが考えどころなわけです。
ただ、
月末の日付は、
エクセルで月末の日付を計算する
で書いているとおり、
「（求めたい月末の月の）翌月初の日付（のシリアル値）－１」
で計算できます。
この考え方を使うと、
| 仕入月の翌月 | の末日 | の日付を求めたい |
| ↓ | ||
| 仕入月の翌々月 | の１日 | の日付の前日を求めたい |
と変型できます。
結果、
次のような感じで計算式化できます。
