１日から始まり月末までを経理上の月度としない場合の月度の求め方

• 相対日付の半期項目の日付を4/1ではなく、任意の日付(4/9)に設定したいのですが、可能でしょうか。。

弊社も締め日が20日なので、標準の相対日付で月度を求めることができないので、数式項目で対処しています。

弊社では21日からスタートなので以下のような式です。jc__cが日付の項目です

IF( DAY( jc__c ) < 21, TEXT( YEAR( jc__c ) *100 + MONTH(jc__c ) ) ,
IF ( MONTH(jc__c ) = 12, TEXT( YEAR( jc__c ) + 1) & '01' ,
TEXT( YEAR( jc__c ) *100 + MONTH(jc__c ) + 1)
)
)

9日で月度が変わるようにするには以下のように設定してみてください。

IF( DAY( jc__c ) < 9, TEXT( YEAR( jc__c ) *100 + MONTH(jc__c ) ) ,
IF ( MONTH(jc__c ) = 12, TEXT( YEAR( jc__c ) + 1) & '01' ,
TEXT( YEAR( jc__c ) *100 + MONTH(jc__c ) + 1)
)
)

年月度から今年度の半期と昨年度の半期を求める式は以下のようになります。

IF(AND( Nengetu__c >=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())-1) & "04",
2,TEXT(YEAR(TODAY())-1) & "04",
3,TEXT(YEAR(TODAY())-1) & "04",
TEXT(YEAR(TODAY())) & "04") ,
Nengetu__c <=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())) & "09",
2,TEXT(YEAR(TODAY())) & "09",
3,TEXT(YEAR(TODAY())) & "09",
TEXT(YEAR(TODAY())+1) & "09") ),"this1",
IF(AND( Nengetu__c >=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())-1) & "10",
2,TEXT(YEAR(TODAY())-1) & "10",
3,TEXT(YEAR(TODAY())-1) & "10",
TEXT(YEAR(TODAY())) & "10") ,
Nengetu__c <=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())) & "03",
2,TEXT(YEAR(TODAY())) & "03",
3,TEXT(YEAR(TODAY())) & "03",
TEXT(YEAR(TODAY())+1) & "03") ),"this2",
IF(AND(Nengetu__c >=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())-2) & "04",
2,TEXT(YEAR(TODAY())-2) & "04",
3,TEXT(YEAR(TODAY())-2) & "04",
TEXT(YEAR(TODAY())-1) & "04") ,
Nengetu__c <=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())-1) & "09",
2,TEXT(YEAR(TODAY())-1) & "09",
3,TEXT(YEAR(TODAY())-1) & "09",
TEXT(YEAR(TODAY())) & "09") ),"last1",
IF(AND(Nengetu__c >=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())-2) & "10",
2,TEXT(YEAR(TODAY())-2) & "10",
3,TEXT(YEAR(TODAY())-2) & "10",
TEXT(YEAR(TODAY())-1) & "10") ,
Nengetu__c <=
CASE(MONTH(TODAY()),
1,TEXT(YEAR(TODAY())-1) & "03",
2,TEXT(YEAR(TODAY())-1) & "03",
3,TEXT(YEAR(TODAY())-1) & "03",
TEXT(YEAR(TODAY())) & "03") ),"last2",
"other"))))