入力された契約開始日を元に契約満了日を計算する数式

これはなに

レコード内に入力された「契約開始日」と「契約月数」を元に「契約満了日」を算出する数式。
もともと https://non-prog-sf.hatenablog.com/entry/2015/05/29/231820 に記載されている数式をずっとありがたく使わせてもらっていたのですが、Spring'18から「ADDMONTHS関数」がリリースされており、そちらを使って数式を作成できればシンプルになるかつコンパイル数も節約できるのでは？と作ってみました。

なお、契約満了日の算出は民法143条に則ります。

民法第１４３条
１　週、月又は年によって期間を定めたときは、その期間は、暦に従って計算する。
２　週、月又は年の初めから期間を起算しないときは、その期間は、最後の週、月又は年においてその起算日に応当する日の前日に満了する。ただし、月又は年によって期間を定めた場合において、最後の月に応当する日がないときは、その月の末日に満了する。

2022/08/31 追記

なんと1行で実現できることが @stomita さんからのコメントにより発覚しました。

CloseDate: 契約開始日
ContractTerm__c: 契約月数

ADDMONTHS(CloseDate - 1, ContractTerm__c) 

以上です。
これだけシンプルになると"契約終了日に対してさらに条件を追加したい/契約終了日から別の日付を導きたい"などの要望にもかなり楽に対応できそうです。コメントくださりありがとうございます！

これ以降の内容は私が↑の数式にたどり着けずじたばたしている記録になっております、最初に思いついた方法にとらわれると新しい発想ができなくなる好例としてお収めください。

数式

CloseDate: 契約開始日
ContractTerm__c: 契約月数

EndDateFormula

/* 2月のイレギュラー処理 */
/* 開始日が29日で終了月が2月かつうるう年の場合 */
IF(
(DAY(CloseDate) == 29 && MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) == 2 && MOD(YEAR(ADDMONTHS(CloseDate,ContractTerm__c)),4) == 0),
ADDMONTHS(CloseDate, ContractTerm__c) -1,

/* 開始日が29か30か31日で終了月が2月の場合nヶ月後の末日 */
IF(
(DAY(CloseDate) > 28 && MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) == 2),
ADDMONTHS(CloseDate, ContractTerm__c),
/* 2月のイレギュラー処理 終了*/

/* condition */
IF(MONTH(CloseDate + 1) == MONTH(CloseDate),

/* 開始日が末日でない場合はnヶ月後の日付から-1 */
ADDMONTHS(CloseDate, ContractTerm__c)-1,

/* condition */
IF(
(MONTH(CloseDate) = 1 || MONTH(CloseDate) = 3 || MONTH(CloseDate) = 5 || MONTH(CloseDate) = 7 || MONTH(CloseDate) = 8 || MONTH(CloseDate) = 10 || MONTH(CloseDate)= 12) &&
(MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 4 || MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 6 || MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 9 || MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 11),

/* 開始日が末日かつ */
/* 終了月に応当日がない場合はnヶ月後の末日*/
ADDMONTHS(CloseDate, ContractTerm__c),

/* 終了月に応当日がある場合はnヶ月後の開始日のDAYから-1 */
DATE(
YEAR(ADDMONTHS(CloseDate, ContractTerm__c)),
MONTH(ADDMONTHS(CloseDate, ContractTerm__c)),
DAY(CloseDate)
) - 1
)
)
)
)

数式内容解説

ADDMONTHS関数の仕様(微妙にかゆいところを外してくる)

• 指定した日付の指定の月数前または後の日付を返す
• 指定された日付が月の最終日の場合、結果の日付は、結果の月の最終日になる
• 指定された日付が結果の日付の月に存在しない場合、結果の日付は、結果の月の最終日になる

なぜ2番目と3番目の仕様を同居させてしまったのかは謎ですが、この仕様に沿うと

• 最終月に応当日ありパターン(シンプルに同じ日付を返す)
  • 11/30 の5ヶ月後 → 4/30
• 最終月に応当日なしパターン(最終月の末日を返す)
  • 12/31 の4ヶ月後 → 4/30
• 契約開始日が月末パターン(問答無用で最終月の末日を返す)
  • 4/30 の1ヶ月後 → 5/31
  • 2/28 の1ヶ月後 → 3/31

と契約期間の終わりが伸びたり縮んだりしてしまうので
この仕様を民法にある
起算日に応当する日の前日に満了する
最後の月に応当する日がないときは、その月の末日に満了する
という条件に合うように組みます。

基本動作

開始日が末日でさえなければADDMONTHS関数はそのまま使えるので
開始日に1日をプラスしたときに月が変わらないか、で末日かどうかを判定します。

/* condition */
IF(MONTH(CloseDate + 1) == MONTH(CloseDate),

開始日(=起算日)の前日にしたいので-1します。

/* 開始日が末日でない場合はnヶ月後の日付から-1 */
ADDMONTHS(CloseDate, ContractTerm__c)-1,

開始日が末日の場合

応当日の有無を確認する必要があります。
31日まである月: 1月 3月 5月 7月 8月 10月 12月
30以下で終わる月: 2月 4月 6月 9月 11月
つまり、31日まである月から始まる かつ 30以下で終わる月が終了日だと応当日がないということになります。

IF(
(MONTH(CloseDate) = 1 || MONTH(CloseDate) = 3 || MONTH(CloseDate) = 5 || MONTH(CloseDate) = 7 || MONTH(CloseDate) = 8 || MONTH(CloseDate) = 10 || MONTH(CloseDate)= 12) && 
(MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 4 || MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 6 || MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 9 || MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) = 11),

これをADDMONTHS関数の「指定された日付が月の最終日の場合、結果の日付は、結果の月の最終日になる」を使い、末日を計算します。

/* 開始日が末日かつ */
/* 終了月に応当日がない場合はnヶ月後の末日*/
ADDMONTHS(CloseDate, ContractTerm__c),

逆に応当日がある場合、ADDMONTHS関数をそのまま使ってしまうと強制的に末日になってしまうため、
年と月はADDMONTHS関数を使って計算しつつ、開始日の日を取得し、そこからマイナス1日します。
例) 11/30 の1ヶ月後は 12/29 となってほしい

/* 終了月に応当日がある場合はnヶ月後の開始日のDAYから-1 */
DATE(
YEAR(ADDMONTHS(CloseDate, ContractTerm__c)),
MONTH(ADDMONTHS(CloseDate, ContractTerm__c)),
DAY(CloseDate)
) - 1

ここまでがベースの計算になります。

最後にどうしても2月の計算をイレギュラーケースとして先に処理してしまわないとうまくいかなかったので、先に計算を持ってきました。
同じ結果文を複数出すことになってしまって気持ち悪いので、誰かシュッとまとめられる人がいたらコメントで教えてもらえると嬉しいです。

2月の処理

/* 2月のイレギュラー処理 */
/* 開始日が29日で終了月が2月かつうるう年の場合 */
IF(
(DAY(CloseDate) == 29 && MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) == 2 && MOD(YEAR(ADDMONTHS(CloseDate,ContractTerm__c)),4) == 0),
ADDMONTHS(CloseDate, ContractTerm__c) -1,

/* 開始日が29か30か31日で終了月が2月の場合nヶ月後の末日 */
IF(
(DAY(CloseDate) > 28 && MONTH(ADDMONTHS(CloseDate,ContractTerm__c)) == 2),
ADDMONTHS(CloseDate, ContractTerm__c),
/* 2月のイレギュラー処理 終了*/

最後に

日付計算に特化した関数なんだからそれ使えばすぐに数式なんて組めるやろ、と思っていた自分にとんでもない時間(延べ15時間くらい)かかるぞと伝えに行きたいです。

シンプルになったかはかなり微妙ですが、一応コンパイル数はもともと使わせてもらっていた数式が5,190文字、今回作成した数式が2,304文字と半分くらいには圧縮できました。

月途中からの契約を許容している企業の方に使ってもらえたら嬉しいです。