特定の日を求める数式


# 当月の末日、翌月の末日を求める。
基本的には翌月あるいは翌々月の1日を求めて1日引くやりかたで行う。(Salesforceでなくても基本的には同じ）

当月の末日

```
DATE( Year(ADDMONTHS(TODAY(),1)),MONTH( ADDMONTHS(TODAY(),1)),1 ) -1
```

翌月の末日

```
DATE( Year(ADDMONTHS(TODAY(),2)),MONTH( ADDMONTHS(TODAY(),2)),1 ) -1
```


# 第二営業日を求める

元の質問 : [Formula to get the second business day of each month](https://trailhead.salesforce.com/en/trailblazer-community/feed/0D54S00000JdZpo)

当月の場合

```
CASE(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),2)),
1,DATE(YEAR(TODAY()),MONTH(TODAY()),3),
7,DATE(YEAR(TODAY()),MONTH(TODAY()),4),
DATE(YEAR(TODAY()),MONTH(TODAY()),2)) )
```

翌月の場合

```
IF ( MONTH(TODAY()) != 12,

CASE(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,2)),
1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,3),
7,DATE(YEAR(TODAY()),MONTH(TODAY())+1,4),
DATE(YEAR(TODAY()),MONTH(TODAY())+1,2)) ),

CASE(WEEKDAY(DATE(YEAR(TODAY())+1,1,2)),
1,DATE(YEAR(TODAY())+1,1,3),
7,DATE(YEAR(TODAY())+1,1,4),
DATE(YEAR(TODAY())+1,1,2)) )
)
```

# その週の特定の曜日を求める

[その週の特定の曜日を求める](https://trailhead.salesforce.com/en/trailblazer-community/feed/0D54S00000Jdnqb)

この例はフローで使うもの
```
$Flow.CurrentDate +
CASE( WEEKDAY ( $Flow.CurrentDate ),
1,4,
2,3,
3,2,
4,1,
5,0,
6,-1,
-2)
```

# 毎月の第二火曜日を求める数式

毎月の第二火曜日を求める質問でした。

基本的な考え方はその月の1日の曜日から数えていく。

WEEKDAYは日曜日が1で土曜日が7ということで
1なら2を足す
3ならそのまま(0を足す）
7なら-4

というようにして第二なので14を足します。

```
DATE(YEAR(TODAY()),MONTH(TODAY()),1) + 
CASE(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)),
1,16,
2,15,
3,14,
4,13,
5,12,
6,11,10)
```

[Date field that updates every two weeks](https://trailhead.salesforce.com/en/trailblazer-community/feed/0D54S00000BshcdSAB)


# 毎月の最終の水曜日を求める数式

+ [How to find last Wednesday of each month?](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007XI7e7SAD)

```
IF( MONTH(Day__c) = 12,
DATE(YEAR(Day__c)+1,1,1) - 
CASE(WEEKDAY(DATE(YEAR(Day__c)+1,1,1)),
1,4,
2,5,
3,6,
4,7,
5,1,
6,2,
3)
,
DATE(YEAR(Day__c),MONTH(Day__c)+1,1) - 
CASE(WEEKDAY(DATE(YEAR(Day__c),MONTH(Day__c)+1,1)),
1,4,
2,5,
3,6,
4,7,
5,1,
6,2,
3)
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/328136/8c8f8677-153d-d401-bbc4-4744e4cecc68.png)


# 前週の月曜日の日付を確認する

+ [How to find previous week Monday date? Since the start of week can be Sunday or Monday so how to write code so that it works for all users](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007XIbUl)

```
TODAY()-WEEKDAY(TODAY()-1)+1-7
```


# 契約開始から1年を迎える顧客を「3ヶ月前」の時点で把握する

+ [【レポート】nか月経過しているレコード一覧を出す場合](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007X9pTE)

```
IF( YEAR(ADDMONTHS( 利用開始日,9)) = YEAR(TODAY()) &&
    MONTH(ADDMONTHS( 利用開始日,9)) = MONTH(TODAY()) ,1,0)
```

# DateTime 型

+ [Tomorrow Date/Time](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007ak5fl)

:::note
9/24 is an example of Japan time.
Please adjust to your local time.
 
```
DATETIMEVALUE( TEXT( TODAY() +1) & " 08:00:00" ) + 9/24
```
:::

# 1.6とか少数で表した日付

+ [Help with date formula.](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007cm97P)

:::note
AND(VALUE(RIGHT(TEXT(Loan_Term__c),FIND(".", REVERSE(TEXT(Loan_Term__c))) - 1))<12,
MOD((Loan_Term__c-FLOOR(Loan_Term__c))*100,1)=0)
:::

# 毎月の第一月曜日の７日前を求める

+ [Validation Criteria Help - Monday of every month](https://trailhead.salesforce.com/trailblazer-community/feed/0D5KX000000VV9Y0AW)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/328136/08f4143c-e6f8-47ec-a134-6a6f7725de18.png)