【salesforce】◯ヶ月契約の契約終了日を算出する数式
※2016年3月29日にアップデートしました。
海外のsalesforceのフォーラムに投稿されていたネタです。こういうのをどうやって作ったらいいという質問があったのです。
例えば2015/6/1が契約開始の12ヶ月契約の場合、契約終了日は2016/5/31となります。原則、終了日は6/1ではなく1日前の5/31になります。
この例はまだ簡単なのですが、2015/1/31の場合、
- 1ヶ月契約：2015/2/28
- 2ヶ月契約：2015/3/30
- 13ヶ月契約：2016/2/29
となります。うるう年ですね。一方2015/2/1の場合は、
- 1ヶ月契約：2015/2/28
- 2ヶ月契約：2015/3/31
- 13ヶ月契約：2016/2/29
となる。このロジックが結構ややこしく、答えるのに2日かかりました。
先に答えを貼っておくと、
start_date__c：契約開始日
Term_Months__c：契約期間（ヶ月）
で、契約開始日の年、月、日をそれぞれ計算する、
s_year__c：契約開始日の年（year(start_date__c)）
s_month__c：契約開始日の月（month(start_date__c)）
s_day__c：契約開始日の日（day(start_date__c)）
というカスタム項目を合わせて用意します。この3つの形式は数値です。
----------------------
IF (s_day__c >29&&(s_month__c + Term_Months__c=2||s_month__c + mod(Term_Months__c,12)=2||s_month__c + mod(Term_Months__c,12)-12=2),
IF (s_month__c =1 ,
date(s_year__c + FLOOR( Term_Months__c /12) ,3,1)-1,
date(s_year__c + FLOOR( Term_Months__c /12)+1 ,3,1)-1
) ,
IF (s_day__c =1,
IF ( s_month__c + mod(Term_Months__c,12)>12 ,
date(s_year__c + FLOOR( Term_Months__c /12)+1 ,s_month__c + mod(Term_Months__c,12)-12,1)-1 ,
date(s_year__c + FLOOR( Term_Months__c /12) ,s_month__c + mod(Term_Months__c,12),1)-1
) ,
IF ( s_month__c + mod(Term_Months__c,12)>12 ,
date(s_year__c + FLOOR( Term_Months__c /12)+1 ,s_month__c + mod(Term_Months__c,12)-12,s_day__c-1 ) ,
date(s_year__c + FLOOR( Term_Months__c /12) ,s_month__c + mod(Term_Months__c,12),s_day__c-1)
)
)
)
----------------------
これを数式を日付形式にしてコピペすれば使えます。
一応解説。
1)基本的な枠組み
date(s_year__c+ FLOOR( Term_Months__c /12) ,s_month__c+ mod(Term_Months__c,12),s_day__c-1
です。date関数を使って、年、月、日を計算します。
年：開始日の年+契約期間/12ヶ月の商（FLOOR関数を使います）
月：開始日の月+契約期間/12ヶ月の余り（mod関数を使います）
日：開始日の日-1日
です。
例えば2015/03/15の14ヶ月後は、
年：2015+FLOOR(14/12)=2015+1=2016
月：3+mod(14,12)=3+2=5
日：15-1=14
ということで2016/05/14となります。
ただし、月の計算で12を上回ることも十分に考えられます。11月の3ヶ月後は
11+mod(3,12)=11+3=14
となってしまうようにです。
ここでIF関数による月の計算で12を超えるか超えないかという分岐が発生します。
s_month__c+ mod(Term_Months__c,12) >12
超えない場合は上記の数式、超える場合は、
年：開始日の年+契約期間/12ヶ月の商+1
月：開始日の月+契約期間/12ヶ月の余り-12ヶ月
日：開始日の日-1日
という計算をします。2015/11/15の3ヶ月後は
年：2015+FLOOR(3/12)+1=2015+0+1=2016
月：11+mod(3,12)-12=11+3-12=2
日：15-1=14
ということで2016/02/15となります。
2)月初1日だった場合
このケースだと1)のパターンが使えないので、数式を変えなくてはなりません。日の計算で1-1では0になるので。具体例を出すと、2015/01/01の3ヶ月後は2015/04/01から1日引いた2015/03/31として日付全体から1日引くのです。
date(s_year__c+ FLOOR( Term_Months__c /12) ,s_month__c+ mod(Term_Months__c,12),1)-1
カッコの位置がポイントですね。また日のところは必ず1日の場合はこの数式で計算することを前提にしているので1と数字を直接入れます。コンパイル数を少しでも減らすためです。
また、契約が年またぎした時の判断としては以下のとおりです。
s_month__c+ mod(Term_Months__c,12)>12
これは12/31が終了日になる場合、翌年の01/01から1日引かなければいけないためです。
3)契約終了日が2月28日か29日の場合
これは1のケースが使えません。31日から1日引いた場合02/30となってしまいエラーを返してしまうのです。必然的に03/01から1日引く計算式にする必要があります。こうすることでうるう年も関係なく28日か29日で計算できます。
この判断はまず開始日の日が30日か31日であること（1の計算を2月で行った時にエラーになる可能性がある日）、かつ契約終了日が2月であることが条件になります。2月になるのは3パターンあります。
• 開始日の月+契約期間=2月（1月+1ヶ月のみ）
• 開始日の月+契約期間/12ヶ月の余り=2月（1月+13ヶ月など）
• 開始日の月+契約期間/12ヶ月の余り-12ヶ月=2月（2月+12ヶ月など）
これが条件式になります。
s_day__c>29&&(s_month__c+ Term_Months__c=2||s_month__c+ mod(Term_Months__c,12)=2||s_month__c+ mod(Term_Months__c,12)-12=2)
さらに、2/28や2/29になるパターンは、開始日が1/30および1/31と、それ以外の月では計算が異なります。
IF (s_month__c=1,
date(s_year__c+ FLOOR( Term_Months__c /12) ,3,1)-1,
date(s_year__c+ FLOOR( Term_Months__c /12)+1 ,3,1)-1)
1月以外の月は確実に2月が来るのは翌年なのです。つまり3/31の場合、最短11ヶ月で2/28（2/29）が来るのですが、FLOOR( 11/12)は0になってしまうので、1ヶ月分追加しておくのです。ちなみに2/28（2/29)の12ヶ月後は、もともとs_day__c>29の条件から外れるため、今回の計算式で計算されず、1の数式で計算されるようになっています。
もう、長々と書いたのですが、簡単に「12ヶ月契約とか入れられるようにならない？」とか要望が出たとしても、どうにかこれで叶えられると思いますが、ものすごいロジックを動かして、すごい手間がかかるということだけご理解ください。
