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

はじめに

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

あるオブジェクトに、以下の項目がある。
- 年/月を項目値とした選択リスト項目（項目値として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文字以下となり、利用可能になりました。