Mark Hammer's Blog

SalesforceやTrailheadに関する情報を投稿しているブログです。

選択リストを基に作った日付から年度判定をしたい

はじめに

以下のような質問を受けました。

  • あるオブジェクトに、以下の項目がある。
    • 年/月を項目値とした選択リスト項目(項目値として2022/01、2022/02、…のように並んでいる)
    • 上記選択リスト項目を基に、年月は選択リスト項目値のものを、日は15日に固定した数式(日付)項目
      • 例:選択リスト項目が「2022/1」の場合は「2022/1/15」となる。
  • この時、数式(日付)項目が今年度かどうかを判定する数式項目を作りたい。

この数式作りに結構苦戦したのでここに残しておきます。

数式作り

前提

数式に用いる項目のAPI参照名及び仕様は以下とします。

  • 年/月を項目値とした選択リスト項目: picklist_date__c
    • 選択リスト項目値は2022/01、2022/02、…のように、月の部分は2桁固定とします。
  • picklist_date__c から作成した数式(日付)項目:formula_date__c
    • 数式は DATE(VALUE(LEFT(TEXT(picklist_date__c),4)), VALUE(RIGHT(TEXT(picklist_date__c),2)), 15) とします。
  • 今年度か判定する数式項目:chk_thisyear__c (テキスト項目)
    • 今年度の場合は「今年度」、そうでない場合は「今年度以外」と表示します。

年度が固定の場合

これは formula_date__c が該当年度(〇年4月1日~〇+1年3月31日)の間であることをIF関数を用いてチェックすればOKです。
例として、2021年度か否かを判定する場合は以下数式になります。

IF(AND(formula_date__c >= DATE(2021,4,1), formula_date__c <= DATE(2022,3,31)),
"今年度", "今年度以外")

年度が「今日」を基準にする場合

これは年度固定の場合より少し複雑になります。
なぜなら今日の月によって年の判定が変わるからです。

  • 今日が1月~3月の場合
    • formula_date__c が去年の4月1日から今年の3月31日までなら「今年度」
  • 今日が4月~12月の場合
    • formula_date__c が今年の4月1日から来年の3月31日までなら「今年度」
formula_date__c を使った数式

そこで、以下のように今日の月によって判定が変わるようIF関数を追加して、以下の数式を作成します。

IF(MONTH(TODAY())<=3, 
    IF(AND(formula_date__c >= DATE(YEAR(TODAY())-1,4,1), formula_date__c <= DATE(YEAR(TODAY()),3,31)),
    "今年度", "今年度以外"),
    IF(AND(formula_date__c >= DATE(YEAR(TODAY()),4,1), formula_date__c <= DATE(YEAR(TODAY())+1,3,31)),
    "今年度", "今年度以外")
)

すると、数式サイズが5000文字オーバーとしてエラーになってしまいました。

formula_date__c を使うパターンではこれ以上簡単な数式を作れなかったので、再考を求められました。

formula_date__c を使わない数式

formula_date__c を使わないとなると、picklist_date__cから年、月を取り出して、そこから今日の日付と比較を行う必要があります。
また、数式サイズ削減のためDATE関数を使用できないので、年と月を個別に比較する必要があります。
考えた結果、以下の表を作成してからそれを数式に当てはめました。

picklist_date__c
1~3月 4~12月
今日 1~3月 年が同じ picklist_date__cの年が
今日の年の1年前
4~12月 picklist_date__cの年が
今日の年の1年後
年が同じ
  • 数式
IF( 
    OR(
        AND(MONTH(TODAY())<=3,VALUE(right(TEXT(picklist_date__c),2))<=3,VALUE(left(TEXT(picklist_date__c),4))=YEAR(TODAY())),
        AND(MONTH(TODAY())>=4,VALUE(right(TEXT(picklist_date__c),2))>=4,VALUE(left(TEXT(picklist_date__c),4))=YEAR(TODAY())),
        AND(MONTH(TODAY())<=3,VALUE(right(TEXT(picklist_date__c),2))>=4,VALUE(left(TEXT(picklist_date__c),4))=YEAR(TODAY())-1),
        AND(MONTH(TODAY())>=4,VALUE(right(TEXT(picklist_date__c),2))<=3,VALUE(left(TEXT(picklist_date__c),4))=YEAR(TODAY())+1)
    ),
    "今年度", "今年度以外")

これで数式サイズも5000文字以下となり、利用可能になりました。