Salesforceで日付範囲の平日を計算することを考える機会があったので、メモとしてまとめておきます。
はじめに
Salesforceで日付範囲の営業日等を計算する際に、まずは平日を算出する作業をするかと思います。
Salesforce Helpで2つの日付間の平日の日数を確認という数式が紹介されていますが、実際に計算してみるとズレるらしく、新しい計算方法を考える必要があります。
Salesforce Helpの記事が、WEEKDAY関数で完結するものを回りくどい別の方法で処理している等、少し古い内容みたいです。
最終的な数式
先に出来上がった数式を示します。
( 終了日 - 開始日 + WEEKDAY( 開始日 ) - WEEKDAY( 終了日 ) ) * 5 / 7
+ MIN( 7 - WEEKDAY( 開始日 ) , 5 ) + MIN( WEEKDAY( 終了日 ) - 1 , 5 ) - 5
考え方のプロセス
考え方のプロセスとしては、下記の順序になります。
- 日付範囲を分割する
- 週が全て含まれる範囲の平日を計算する
- 開始日が含まれる週の平日を計算する
- 終了日が含まれる週の平日を計算する
- 式を整理する
日付範囲を分割する
計算する日付範囲を
- 週が全て含まれる範囲（開始日の次の日曜日から終了日の前の土曜日）
- 開始日が含まれる週
- 終了日が含まれる週
に分割します。例えば、2023年8月21日から2023年10月20日の範囲であれば、
- 週が全て含まれる範囲（2023年8月27日~2023年10月14日）
- 開始日が含まれる週（2023年8月21日~2023年8月26日）
- 終了日が含まれる週（2023年10月15日~2023年10月20日）
に分割します。
週が全て含まれる範囲の平日を計算する
週が全て含まれる範囲は、開始日の次の日曜日と終了日の前の土曜日を計算することで求めることができるため、開始日と開始日の次の日曜日の間の日数と終了日と終了日の前の土曜日の間の日数の対応表を作ります。
| 曜日 | WEEKDAY関数 | 開始日と開始日の次の日曜日の間の日数 | 終了日と終了日の前の土曜日の間の日数 |
|---|---|---|---|
| 日 | 1 | 7日 | 1日 |
| 月 | 2 | 6日 | 2日 |
| 火 | 3 | 5日 | 3日 |
| 水 | 4 | 4日 | 4日 |
| 木 | 5 | 3日 | 5日 |
| 金 | 6 | 2日 | 6日 |
| 土 | 7 | 1日 | 7日 |
対応表の関係から、開始日と開始日の次の日曜日の間の日数はWEEKDAY関数を逆転し、終了日と終了日の前の土曜日の間の日数はWEEKDAY関数のままで使うことができそうです。よって、週が全て含まれる範囲の開始日は
開始日 + ( 7 - WEEKDAY( 開始日 ) + 1 )
週が全て含まれる範囲の終了日は
終了日 - WEEKDAY( 終了日 )
として計算することができるため、週が全て含まれる範囲の日数は
( 終了日 - WEEKDAY( 終了日 ) ) - ( 開始日 + ( 7 - WEEKDAY( 開始日 ) + 1 ) ) + 1
として計算することができます。この結果を7で割ると週が全て含まれる範囲の週の数が得られるため、平日の数は
( ( 終了日 - WEEKDAY( 終了日 ) ) - ( 開始日 + ( 7 - WEEKDAY( 開始日 ) + 1 ) ) + 1 ) * 5 / 7
となります。
開始日が含まれる週の平日を計算する
開始日の曜日と開始日が含まれる週の平日の日数の対応表は下記のようになります。
| 開始日の曜日 | WEEKDAY関数 | 開始日が含まれる平日の日数 |
|---|---|---|
| 日 | 1 | 5日 |
| 月 | 2 | 5日 |
| 火 | 3 | 4日 |
| 水 | 4 | 3日 |
| 木 | 5 | 2日 |
| 金 | 6 | 1日 |
| 土 | 7 | 0日 |
対応表の関係から、開始日が含まれる平日の日数は、WEEKDAY関数を逆転した関数に対して並行移動と上限を設定することで求められることがわかるため、
MIN( 7 - WEEKDAY( 開始日 ) , 5 )
として計算ができます。
終了日が含まれる週の平日を計算する
終了日の曜日と終了日が含まれる週の平日の日数の対応表は下記のようになります。
| 終了日の曜日 | WEEKDAY関数 | 終了日が含まれる平日の日数 |
|---|---|---|
| 日 | 1 | 0日 |
| 月 | 2 | 1日 |
| 火 | 3 | 2日 |
| 水 | 4 | 3日 |
| 木 | 5 | 4日 |
| 金 | 6 | 5日 |
| 土 | 7 | 5日 |
対応表の関係から、終了日が含まれる平日の日数は、WEEKDAY関数に対して並行移動と上限を設定することで求められることがわかるため、
MIN( WEEKDAY( 終了日 ) - 1 , 5 )
として計算ができます。
式を整理する
これまでに得られた式を足し合わせると、
( ( 終了日 - WEEKDAY( 終了日 ) ) - ( 開始日 + ( 7 - WEEKDAY( 開始日 ) + 1 ) ) + 1 ) * 5 / 7
+ MIN( 7 - WEEKDAY( 開始日 ) , 5 ) + MIN( WEEKDAY( 終了日 ) - 1 , 5 )
が得られます。この式を少し整理すると、
( 終了日 - 開始日 + WEEKDAY( 開始日 ) - WEEKDAY( 終了日 ) ) * 5 / 7
+ MIN( 7 - WEEKDAY( 開始日 ) , 5 ) + MIN( WEEKDAY( 終了日 ) - 1 , 5 ) - 5
となります。
補足
平日の計上は様々な方法を考えることができます。例えば、開始日の曜日と終了日の曜日を一致させるように終了日を移動させて計算を行い、後で終了日の移動によって余剰または不足した平日を計算する方法が考えられます。こちらの考え方の方は2種類に分割するだけなので、本記事の3種類に分割するよりも考える手数が少なくて済みます。ただ、この方法で自然な流れで導こうとすると、大抵の場合は条件分岐が必要になり、
( 終了日 - 開始日 + WEEKDAY( 開始日 ) - WEEKDAY( 終了日 ) ) * 5 / 7
+ WEEKDAY( 終了日 ) - WEEKDAY( 開始日 ) + 1 + IF( WEEKDAY( 終了日 ) = 7 , - 1 , 0 ) + IF( WEEKDAY( 開始日 ) = 1 , - 1 , 0 )
というような形になります。最終的な結果との比較で2行目同士が等しくなりますが、関数の使用回数とコンパイル後の文字数が最終的な結果の方が短くなることが違いとして挙げられます。