More than 1 year has passed since last update.

月数の計算をする式

Salesforce

Last updated at 2022-10-06Posted at 2021-09-28

案外ニーズがあるみたいです。

基本的な考え方は年、月、日　に分けて計算します。また日にちの差で少々調整が必要ですね。

この例は15日以上の日付の間隔があいていれば、１か月プラスします。

((YEAR(termination_date__c) - YEAR(ad_installed__c)) * 12) + (MONTH(termination_date__c) - MONTH(ad_installed__c)) + IF (ABS(DAY(termination_date__c) - DAY(ad_installed__c)) => 15, 1, 0)

Formula Field calculating wrong value

何週目かを計算する

Calculating Case age- without weekends

CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)

+ (FLOOR((ClosedDate - Datevalue(CreatedDate))/7)*5)

-

IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)), 

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)

+ (FLOOR((Today() - Datevalue(CreatedDate))/7)*5)

-

IF(OR(

NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up