日付数式のサンプル
サンプル数式を使用して、日付と時間で計算を実行します。
必要なエディション
| 使用可能なインターフェース: Salesforce Classic と Lightning Experience の両方 |
| 使用可能なエディション: すべてのエディション |
このトピックの数式の例を使用するときは、次の考慮事項に留意してください。
- 数式をコピーするときの構文エラーを回避するには、次のように置き換えます。
-
CloseDate などの日付項目や
TODAY()関数など、 実際の DATE 値を含む date プレースホルダー 。 -
CreatedDate や
NOW()など、実際の日付/時間値を持つ date_time プレースホルダー。 -
date_1、date_2、earlier_date、later_date、start_date、end_date、start_date_time、end_date_time、num_years、および target_date プレースホルダー (必要に応じて)。
-
DAY()、MONTH()、YEAR()などの関数には日付入力が必要です。日付/時間値から開始する場合は、最初にDATEVALUE()関数を使用して日付に変換します。たとえば、DAY( DATEVALUE( date_time ))です。
うるう年かどうかを確認
この数式は、1 年がうるう年かどうか (つまり、366 日) を決定します。このロジックは、毎日の金銭的関心の計算から薬剤投与量の追跡まで、毎日の精度を必要とする計算に不可欠です。たとえば、うるう年に年間金額を 365 で割ると、日次データが不正確になります。この数式を使用すると、年に応じて計算分母を 365 または 366 に動的に設定できます。
OR(
MOD( YEAR( date ), 400 ) = 0,
AND(
MOD( YEAR( date ), 4 ) = 0,
MOD( YEAR( date ), 100 ) != 0
)
)仕組み:
- 1 年は、2 つの特定の条件のいずれかを満たすうるう年です。1 つの条件は、その年が 400 で完全に割り切れることです (2000 年など)。もう 1 つは、100 ではなく 4 で割り切れることです (2024 年はうるう年ですが、1900 年はうるう年ではありません)。
MOD()関数は、除算の余りを計算します。MOD( Year, 400 ) = 0の場合、その年は 400 で割り切れ、残りはありません。OR()関数は数式全体をラップします。2 つの条件のいずれかが true の場合、数式はTRUEを返します。
日付がどの四半期にあたるかを確認
特定の活動がどの会計四半期に分類されるかを判断することは、売上予測、財務報告、パフォーマンス分析などの活動にとって不可欠です。数式を使用して、標準年度、調整済み会計年度の四半期 (1 ～ 4) を計算したり、日付を当四半期と比較したりできます。
標準会計年度: 第 1 四半期が 1 月に開始する場合、この数式を使用します。
CEILING( MONTH ( date ) / 3 )シフトされた会計年度: この数式は、会計年度が 1 月以外の月で始まる場合に使用します。1 月に合わせてシフトを戻すために必要な月数と一致するADDMONTHS()に –1 の値を調整します。たとえば、示されている数式は、2 月の開始から 1 か月遅れます。
CEILING( MONTH ( ADDMONTHS ( date, -1 ) ) / 3)Check if the Date is in the Current (Standard) Quarter (日付が現在の (標準) 四半期にあるかを確認): この数式は、日付が今日と同じ標準四半期および年に含まれる場合にTRUEを返します。このオプションは、現在の四半期の商談を検証したり、過去のレコードを編集したりできないような状況で役立ちます。
AND(
CEILING( MONTH( date ) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ),
YEAR( date ) = YEAR( TODAY() )
)仕組み:
- これらの数式のロジックでは、月数 (1 ～ 12) を 3 (四半期の月数) で除算します。
-
CEILING()関数は、結果を最も近い整数に切り上げます。例: February (Month 2) を 3 で除算すると 0.66 になり、1 (Q1) に切り上げられます。4 月 (月 4 日) を 3 で割ると 1.33 になり、2 (Q2) に切り上げられます。 -
シフトされた会計年度の場合、
ADDMONTHS()は日付を事実上後ろにシフトし、計算を 1 月の開始に合わせます。たとえば、数式では 1 月を引くと、2 月が 1 月 (サイクルの開始日) になります。 -
日付が当四半期にあるかどうかを確認する場合、数式は、その四半期と年の両方が
TODAY()に一致することを確認し、前年の第 1 四半期の日付に誤って現在のフラグを付けないようにします。
日付が年度の何週目かを確認
週次セールスレポート、サプライチェーン計画、リソース使用状況の追跡などの活動では、週別にレコードをグループ化することが基本的な要件です。週 1 の定義は組織によって異なるため、Salesforce では ISO 標準 (ビジネスレポートに最適) と絶対数 (シンプルでローカライズされた年間進行状況の追跡に最適) の 2 つの方法を提供しています。
ISO 標準週 (推奨): この数式は、ほとんどのビジネスレポートを ISO-8601 標準に合わせるために使用します。週は常に月曜日から始まり、木曜日を含む年の最初の週は週 1 とみなされます。
ISOWEEK( date )Simple Absolute Week Count (単純な絶対週数): 曜日に関係なく、週 1 を常に 1 月 1 日に開始するように組織で定義している場合、この数式を使用します。この方法は、単純な年間カウンターで、年が開始してから経過した 7 日間のブロック数を知りたい場合に便利です。
CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)仕組み:
- ISO 標準ロジック:
ISOWEEK()関数は、ISO-8601 定義に基づいて週番号 (1 ～ 53) を自動的に計算するネイティブツールです。1 月が前年の最終週に開始したり、12 月が翌年の第 1 週に終了したりするエッジケースを自動的に処理します。 - 絶対計数ロジック:
- 数式
date - DATE( YEAR( date ), 1, 1) + 1は、その年の「日数」を計算します。たとえば、2 月 1 日は 32 日です。次に、日数を 7 で除算して、経過した週数を求めます。例を続行すると、32/7 = 4.57 になります。 CEILING()は、結果を最も近い整数に切り上げます。この場合、4.57 は 5 に切り上げられ、2 月 1 日が週 5 日になります。- 365 日は 7 (365 日 ÷ 7 = 52.14) で均等に分割されないため、年の最後の 1 日または 2 日は部分的な週 53 日になります。
- 数式
2 つの日付が同月かどうかを確認
手数料の計算、キャンペーン属性、統合請求などの活動では、多くの場合、2 つのイベントが同じ会計期間または報告期間に発生したかどうかを判断する必要があります。この数式は、2 つの特定の日付フィールド (Opportunity CloseDate とカスタム Invoice_Date__c) が同じカレンダー月内にあるかどうかを確認します。
AND(
MONTH( date_1 ) = MONTH( date_2 ),
YEAR( date_1 ) = YEAR( date_2 )
)また、このロジックを動的に適用して、日付が今日から相対的に当月内にあるかどうかを検証することもできます。この方法は、今月が期日の ToDo を強調表示したり、自動的に更新される「今月」レポートの数式フラグを作成したりといった条件付き書式で役立ちます。
AND(
MONTH( date ) = MONTH( TODAY() ),
YEAR( date ) = YEAR( TODAY() )
)
仕組み:
- この数式では、
MONTH()を使用して両方の日付の数値 (1 ～ 12) を抽出し、比較します。 -
また、
YEAR()を使用して年が一致するようにします。このステップがないと、2024 年 1 月の日付が 2023 年 1 月の日付と誤って一致します。 -
AND()関数では、月と年の両方がTRUEを返すために一致している必要があります。
月の最終日を確認
月末日の計算は、財務と契約管理の使用事例で不可欠です。一般的な使用事例には、動的サブスクリプション終了日の設定、請求書の期日の決定、月末の商談成立に関するレポートなどがあります。この数式は、28 日、30 日、31 日月、およびうるう年の処理を簡単にするために、翌月の初日から 1 日減算します。
ADDMONTHS( DATE( YEAR( date ), MONTH( date ), 1 ), 1 ) - 1仕組み:
- 数式
DATE( YEAR( date ), MONTH( date ), 1 )は、元の日付フィールドから月の 1 日に設定された日付を作成します。 ADDMONTHS( ..., 1 )は、「月の 1 日」の日付を 1 か月正確に進めます。たとえば、1 月 1 日を 2 月 1 日に変更します。- 翌月の初日から 1 を減算すると、日付は自動的に当月の最終日に逆算されます。この変換では、2 月 28 日と 30 日 31 日が自動的に正しく処理されます。
月を数値ではなく文字列として表示
標準の日付項目は 2024 年 1 月 15 日など、数値で表示されますが、レポート作成や顧客対応コミュニケーションでは、より洗練された形式が必要になることがよくあります。この数式は、月の数値をその全文名 (January など) に変換します。この変換は、見積ドキュメント、メールテンプレート、マーケティングニュースレターなどのリソースでプロフェッショナルヘッダー (「1/15」ではなく「1 月 15 日」など) を生成する場合に便利です。
CASE(
MONTH( date ),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
""
)
組織で複数の言語を使用している場合は、月名を翻訳可能なカスタム表示ラベルに置き換えます。この方法では、ユーザーの言語設定に基づいて月名が動的に更新されます。たとえば、米国のユーザーには「January」、フランスのユーザーには「Janvier」と表示されます。
CASE(
MONTH( date ),
1, $Label.Month_of_Year_1,
2, $Label.Month_of_Year_2,
3, $Label.Month_of_Year_3,
4, $Label.Month_of_Year_4,
5, $Label.Month_of_Year_5,
6, $Label.Month_of_Year_6,
7, $Label.Month_of_Year_7,
8, $Label.Month_of_Year_8,
9, $Label.Month_of_Year_9,
10, $Label.Month_of_Year_10,
11, $Label.Month_of_Year_11,
12, $Label.Month_of_Year_12,
""
)
仕組み:
- この数式では、
MONTH()関数を使用して、日付から月 (1 ～ 12) の数値を抽出します。 - 次に、
CASE()関数はその数値を定義済みのリストと比較します。月が 1 の場合は最初の値 ("January" または $Label.Month_of_Year_1) を返し、2 の場合は 2 番目の値を返します (以下同様)。 - 最終行 (%) はデフォルト値です。date フィールドが空白の場合、
CASE()ステートメントはこの行まで移動して空のテキスト文字列を返すため、 エラーが発生しません 。
日付からの曜日の検索と表示
Salesforce では日付が数値 (2024-12-25 など) で保存されますが、外部とのコミュニケーションや内部スケジュールでは、多くの場合、より読みやすい形式が必要になります。この数式は、日付項目から特定の曜日を識別し、テキストに変換します (「水曜日」など)。この関数は、挨拶を「Happy Friday!」 (金曜日おめでとう) のようにパーソナライズしたいメールマーケティングテンプレートで役立ちます。もう 1 つの使用事例として、スタッフ割り当てレポートがあります。これにより、週末の活動を簡単に見つけて絞り込むことができます。
CASE(
WEEKDAY ( date ),
1, "Sunday",
2, "Monday",
3, "Tuesday",
4, "Wednesday",
5, "Thursday",
6, "Friday",
7, "Saturday",
""
)
組織で複数の言語を使用している場合は、日の名前を翻訳可能なカスタム表示ラベルに置き換えます。この方法では、ユーザーの言語設定に基づいて曜日名を動的に変更できます (スペイン語のユーザーには「ドミンゴ」を表示するなど)。
CASE(
WEEKDAY( date ),
1, $Label.Day_of_Week_1,
2, $Label.Day_of_Week_2,
3, $Label.Day_of_Week_3,
4, $Label.Day_of_Week_4,
5, $Label.Day_of_Week_5,
6, $Label.Day_of_Week_6,
7, $Label.Day_of_Week_7,
""
)
仕組み:
WEEKDAY( date )関数は日付を調べ、1 (日曜日) ～ 7 (土曜日) の数値を返します。CASE()関数は、その数値を値のリストと比較します。1 が見つかった場合、最初の値 ("Sunday" または $Label.Day_of_Week_1) を返します。2 が見つかった場合、2 番目の値を返します (以下同様)。- 最終行 (
"") にはデフォルト値があります。date フィールドが空白の場合、CASE()ステートメントはこの行まで到達し、 エラーではなく空のテキスト文字列を返します 。
特定の曜日の次回の日付を確認
一貫した週次スケジュールを設定するために、「次の月曜日」または「次の金曜日」の特定の日付を計算することが重要です。たとえば、サポートチームが毎週金曜日に品質監査を実行したり、請求サイクルが月曜日に開始されたりします。この数式は、レコードの作成日に関係なく、自動的に正しい今後の日付を計算します。
date + ( target_day - WEEKDAY( date ) +
IF( target_day <= WEEKDAY( date ), 7, 0 ) )
target＿day を、必要な日に対応する数値に置き換えます。1＝Sunday、2＝Monday、3＝Tuesday、4＝Wednesday、5＝ Thursday、6＝Friday、7＝Saturday です。
仕組み:
- この数式は、target_day (目的の日) と
WEEKDAY( date )(レコードの現在の日) の差を計算します。 IF()ステートメントは、目標日が今週後半なのか、それとも次の週に折り返すのかを決定します。- 今週後半: 今日が月曜日 (2) で、金曜日 (6) が必要な場合、計算は 6 - 2 = 4 日後になります。
IFは 0 を加算します。 - Past or today (過去または今日): 今日が金曜日 (6) で、月曜日 (2) が必要な場合は、2 – 6 = –4 (月曜日が 4 日前だったことを意味します) になります。今日の目標は「以下」であるため、
IFステートメントでは 7 が結果に追加されます。–4 + 7 = 3 日後 (次の月曜日)。
- 今週後半: 今日が月曜日 (2) で、金曜日 (6) が必要な場合、計算は 6 - 2 = 4 日後になります。
2 つの日付間の日数を確認
営業サイクル時間 (完了予定日 - 作成日) やケース解決時間の追跡など、効率を測定するには、2 つの特定のイベント間の期間を計算します。これらの数式は、正確な経過日数を計算するか、特定の時間しきい値を超えるレコードに自動的にフラグを設定します。
Basic Duration Calculation (基本的な期間の計算): 2 つの特定の日付間の単純な日数を確認するには、早い日付から遅い日付を減算します。
later_date — earlier_dateCheck if a Date Occurred More than 30 Days Ago (日付が 30 日以上前の場合を確認): 日付計算を使用して、期限切れまたは期限切れになったレコードにフラグを設定することもできます。たとえば、この数式を使用して、1 か月間活動がないリードや期日を過ぎた請求書を特定します。日付が今日から過去 30 日以上経過した場合にTRUEを返します。
( TODAY() - date ) > 30仕組み:
- Salesforce では、日付を単純な数値 (整数) として内部に保存します。ある日付から別の日付 (
later_date - earlier_dateなど) を減算すると、Salesforce はそれらの整数の単純な差を計算し、整数の日数として結果を返します。 - 2 番目の数式では、
TODAY() - dateは現在のシステム日付から項目の日付を減算して、レコードの経過時間を日数で計算します。 > 30演算子は、その経過時間をしきい値と比較します。差が 31 日以上の場合、数式はTRUEを返します。それ以外の場合はFALSEを返します。
2 つの日付間の平日の日数を確認
サポートケースの解決や商談の成立までの稼働日数の追跡などの活動の場合、2 つの日付間の営業日数 (平日) を計算します。週末を計数する単純な日付減算とは異なり、この数式では月曜日から金曜日までのみが計数されます。
(
5 * FLOOR( ( end_date - DATE( 1900, 1, 8 ) ) / 7 ) +
MIN( 5, MOD( end_date - DATE( 1900, 1, 8 ), 7 ) )
)
-
(
5 * FLOOR( ( start_date - DATE( 1900, 1, 8 ) ) / 7 ) +
MIN( 5, MOD( start_date - DATE( 1900, 1, 8 ), 7 ) )
)
end_date が後の日付、start_date が正の数を取得する前の日付であることを確認します。
仕組み:
- この数式は、end_date と start_date の平日スコアを計算し、end_date スコアから start_date スコアを減算して、それらの間の平日数を取得します。
- 数式は固定の基準日 (1900 年 1 月 8 日 (月曜日)) を使用します。 既知の月曜日を 0 日目のアンカーとして使用します。
FLOOR( ( end_date - ... ) / 7 )とFLOOR( ( start_date - ... ) / 7 )は、基準日から丸 7 週間経過した日数を計算します。この数式は、この件数に 5 を掛けて、1 週間ごとに 5 営業日をクレジット処理します。MOD()関数は、完全な週が数えられた後の残りの日数 (0 ～ 6) を参照します。MIN( 5, ... )関数では、部分週の平日の件数が 5 に制限されるため、土曜日と日曜日で金曜日より前の平日の合計が増加しません。
2 つの日付間の月数を確認
サブスクリプション管理や契約更新などの活動では、多くの場合、2 つの日付間のカレンダー月数の計算が必要です。たとえば、請求の複数年契約の期間を決定したり、福利厚生の従業員在職期間を計算したりします。この数式は、月と年の値に基づいて差を求め、特定の日を無視します。
( ( YEAR( later_date ) - YEAR( earlier_date ) ) * 12 ) + (MONTH ( later_date ) - MONTH( earlier_date ) )仕組み:
( YEAR( later_date ) - YEAR( earlier_date ) ) * 12は、2 つの日付の年の差を計算し、それを 12 倍して基準月数に変換します。MONTH( later_date ) - MONTH( earlier_date )は、月の数値 (1 ～ 12) の差を計算します。- 最後に、この 2 つの値を加算します。
日付に日数、月数、年数を加算
将来の日付の計算は、ワークフローの自動化の重要な要件です。これらの数式は、日付を将来の日付に動的に予測します。たとえば、電話後 5 日間のフォローアップ ToDo の期限の設定、契約の有効期限の決定、従業員の帰属スケジュールの計算などの活動に使用します。
Add Days (日数を追加): 短期のフォローアップまたは期限をスケジュールするには、その日数を日付フィールドに直接追加します。Salesforce では日付を整数として処理するため、5 を追加すると日付が 5 日増えます。
date + 5Add Months (月を追加): 四半期レビューやサブスクリプション更新などの中期スケジュールでは、ADDMONTHS() を使用します。この関数は、長さの異なる月 (28、30、31 日) を自動的に処理するため、単純な加算を使用するよりもスマートです。
ADDMONTHS( date, 1 )Add Years (年を追加): この数式は、請求の複数年契約期間の決定や給付の従業員在職期間の計算などの計算に使用します。年数を 12 で乗算すると、ADDMONTHS() ロジックを使用して正確な前年比計算を行うことができます。
ADDMONTHS( date, 12 * num_years )num＿years を、追加する年数に置き換えます。
うるう年: デフォルトでは、うるう年 (2 月 29 日) に年を追加し、将来の年がうるう年でない場合、Salesforce は 2 月 28 日を返します。 一部の法的契約で一般的な 2 月 29 日を 3 月 1 日とするビジネス要件がある場合は、次の高度な数式を使用します。
ADDMONTHS( date, 12 * num_years ) +
IF(
AND(
DAY( date ) = 29,
DAY( ADDMONTHS( date, 12 * num_years ) ) = 28
),
1,
0
)
このロジックでは、日付が 28 日に調整されたかどうかが確認され、3 月 1 日にするために 1 日が追加されます。
仕組み:
date + 5数式は、日付の整数値を増やすだけです。ADDMONTHS( date, ... )数式はカレンダーの月を追加します。年を 12 (12 * num_years) で乗算すると、関数で処理できるように年が月に変換されます。- うるう年の数式では、
IF()ステートメントで元の日が 29 日であることと、 計算された将来の日が 28 日であることの 2 つの条件がチェックされます 。両方が true の場合、うるう年以外の年に調整されたことを示します。数式は、日付を 3 月 1 日に前倒しするために 1 日を追加します。
日付に営業日を加算
サービスレベル契約 (SLA) の維持や顧客の期待の管理などの活動では、稼働日に基づいて期限を計算することが重要です。たとえば、サポートで 3 営業日の対応が約束されている場合、金曜日の要求に 3 暦日を追加するだけで、日曜日の期限 (非営業日) が誤って設定されます。この数式は、週末をスキップするように期日を自動的に調整し、要求が常に平日に正しく実行されるようにします。
この例では、3 営業日が追加されます。
CASE(
WEEKDAY( date ),
4, date + 5,
5, date + 5,
6, date + 5,
7, date + 4,
date + 3
)
別の日数を追加するには、CASE() ステートメント内のロジックを調整します。たとえば、4 営業日の調整後の数式を次に示します。
CASE(
WEEKDAY( date ),
1, date + 4, /* Sunday -> Thursday */
2, date + 4, /* Monday -> Friday */
3, date + 6, /* Tuesday -> next Monday */
4, date + 6, /* Wednesday -> next Tuesday */
5, date + 6, /* Thursday -> next Wednesday */
6, date + 6, /* Friday -> next Thursday */
7, date + 5 /* Saturday -> next Thursday */
)
仕組み:
- これらの数式は、標準の月曜日から金曜日の週休を前提としています。特定の休日は考慮されません。数式のアプローチでは、開始日が 0 日目として扱われ、以降の営業日のみが計数されます。
CASE()関数は、開始日 (WEEKDAY( date )) の曜日を確認し、特定のカレンダー日数を追加して、結果が有効な営業日になるようにします。- 3 営業日の数式での調整の処理方法を次に示します。
- 水曜日 (4)、木曜日 (5)、金曜日 (6) の場合: 3 営業日を追加すると、期日が強制的に週末になります。この数式は、ギャップを埋めるために 5 暦日 (3 営業日 + 2 週末) を加算します。
- 土曜日 (7): 日付が土曜日に開始する場合、数式は 4 暦日 (日曜日 + 月曜日/火曜日/水曜日) を加算して水曜日にします。
- 日曜日 (1)、月曜日 (2)、火曜日 (3) の場合: これらの日の場合、3 営業日を追加しても週末は超えません。この数式はデフォルトのケースとして機能し、単純に 3 暦日が追加されます。
日付/時間の時間、分、秒を確認
[日付/時間] 項目から特定の時間成分を抽出することは、詳細なレポートとスケジュールを作成するために重要です。たとえば、営業時間後に作成されたサポートケースにフラグを設定したり、時間帯別に通話量のパターンを分析してスタッフ割り当てを最適化したりします。これらの数式は、正確な時間、分、秒をタイムスタンプから分離します。
- GMT より遅れている場合は、TZoffset を遅れている時間を 24 で割った値に置き換えます。 たとえば、東部時間 (UTC-5) の場合は、5.0 / 24 の値を使用します。
-
GMT より前の場合は、数式のマイナス記号 (–) をプラス記号 (+) で変更します。次に、TZoffset を今後の時間数を 24 で割った値に置き換えます。 たとえば、シンガポール (UTC+8) の場合、8.0 / 24 の値を使用します。
詳細は、「数式での日付、日付/時間、および時間値の使用」の日付/時間およびタイムゾーンに関する注意を参照してください。
Find the Hour (時間を見つける): 次の数式を使用して、時間を数値として抽出します。このアプローチは、「時間帯」分析の主要な方法です。
- 24 時間形式 (0 ～ 23): 計算と並び替えに最適です。
HOUR( TIMEVALUE( date_time - TZoffset ) ) - 12-Hour Format (12 時間形式) (1 ～ 12): ユーザーとのやりとりに最適です。
IF( OR( HOUR( TIMEVALUE( date_time - TZoffset ) ) = 0, HOUR( TIMEVALUE( date_time - TZoffset ) ) = 12 ), 12, MOD( HOUR( TIMEVALUE( date_time - TZoffset ) ), 12 ) )
Find the Minute and Second (分と秒を見つける): 次の数式を使用して分または秒 (0 ～ 59) を抽出します。このアプローチは、正確な期間 ([Time to First Response (最初の応答までの時間)] など) を計算する場合に役立ちます。
- 分:
MINUTE( TIMEVALUE( date_time - TZoffset ) ) - 秒:
SECOND( TIMEVALUE( date_time - TZoffset ) )
Determine AM or PM (AM または PM の判断): この数式を使用して、AM または PM をテキスト文字列として返します。この出力は、メールテンプレートやレポートなど、リソースのカスタム 12 時間タイムスタンプを作成する場合に役立ちます。
IF(
HOUR( TIMEVALUE( date_time - TZoffset ) ) < 12,
"AM",
"PM"
)
Show Full Time as a String (フルタイムを文字列として表示) (HH:MM:SS AM/PM): 09:05:30 AM など、洗練された判読可能な形式で時間を表示する場合は、次の複合数式を使用します。このセクションの他の数式のロジックを組み合わせ、LPAD() を使用して、時間、分、秒の値が常に 2 桁で表示されるようにします (9 ではなく 09 など)。
LPAD( TEXT( IF(
OR(
HOUR( TIMEVALUE( date_time - TZoffset ) ) = 0,
HOUR( TIMEVALUE( date_time - TZoffset ) ) = 12
),
12,
MOD( HOUR( TIMEVALUE( date_time - TZoffset ) ), 12 )
) ), 2, "0" )
& ":" &
LPAD( TEXT( MINUTE( TIMEVALUE( date_time - TZoffset ) ) ), 2, "0" )
& ":" &
LPAD( TEXT( SECOND( TIMEVALUE( date_time - TZoffset ) ) ), 2, "0" )
& " " &
IF( HOUR( TIMEVALUE( date_time - TZoffset ) ) < 12, "AM", "PM" )仕組み:
TIMEVALUE()関数は、日付/時間フィールドから時間部分 (HH:MM:SS) を抽出します。- オフセット (
- 5.0/24など) を減算すると、GMT 時刻が抽出前のローカル時刻にシフトします。 - 12 時間ロジック:
MOD(..., 12)関数は、時間を 12 で除算するときの余りを計算します (たとえば、14 は 2 になります)。IFステートメントは、正午 （12 PM） と午前 0 時 （0 AM） のエッジケースを処理し、0 ではなく 12 として表します。
LPAD(..., 2, "0")では、先頭に 0 が付いた 1 桁の数字がパッドされるため、時刻が標準的に表示されます(たとえば、9:5 ではなく 09:05)。
日付/時間値間の経過時間を確認
パフォーマンスを追跡するには、ワークフローの正確な所要時間を測定する必要があります。たとえば、サポートケースがオープンのままになっている時間 (分単位) を正確に把握することで、サービスレベル契約 (SLA) を適用できます。もう 1 つの例として、リードの作成から最初の営業活動までのタイムラグを追跡することで、効率のギャップを強調します。これらの数式は、この期間を単純な数値または人間が判読可能なテキスト文字列として計算します。
Simple Day Count (単純な日数): 丸 1 日で差を計算する場合は、早い日付から遅い日付を減算します。この方法では、数値が返されます。たとえば、1.5 は 1 日と 12 時間を表します)。
later_date - earlier_dateFormatted Elapsed Time String (Days, Hours, Minutes) (書式設定された経過時間文字列 (日、時間、分): 「2 日 4 時間 15 分」などの判読可能な形式で期間を表示するには、次の数式を使用します。小数部の日付差を特定の時間単位に変換します。
IF(
end_date_time - start_date_time > 0 ,
TEXT( FLOOR( end_date_time - start_date_time ) ) & " days "
& TEXT( FLOOR( MOD( (end_date_time - start_date_time ) * 24, 24 ) ) ) & " hours "
& TEXT( FLOOR( MOD( (end_date_time - start_date_time ) * 24 * 60, 60 ) ) ) & " minutes",
""
)
仕組み:
IFステートメントは、end＿date＿time が実際に start＿date＿time の後にあることを確認します。ユーザーエラーや不正なデータが原因で結果が負の場合、数式は紛らわしい負の文字列ではなく空白値を返します。FLOOR( end_date_time - start_date_time )は差の合計を取得し、整数 (整数) を分離して丸 1 日を数えます。MOD( (diff) * 24, 24 )は残りの小数部 (部分日) を取得し、24 で乗算して時間に変換し、1 日を構成しない時間を分離します。MOD( (diff) * 24 * 60, 60 )は、この差に 1440 (1 日の分数) を乗算して合計分数を取得し、MODを使用して完全な時間を削除してから残りの分数を検索します。時間。- final
FLOOR()は minutes 値を整数として保持し、minutes コンポーネントが 60 に切り上げられないようにします。
2 つの日付/時間値間の営業時間数を確認
サービスレベル契約 (SLA) を正確に追跡するには、多くの場合、未加工の時計時間ではなく「営業時間」で期間を測定する必要があります。たとえば、金曜日の午後 4 時に到着して月曜日の午前 10 時に解決される優先度の高いサポートケースがあるとします。実際の経過時間は 66 時間ですが、9 ～ 5 時間のスケジュールを想定すると、業務時間は 2 時間のみです。この数式は、週末と夜間の非勤務時間を自動的に除外して、特定の営業時間を計算します。
ROUND( 8 * (
( 5 * FLOOR( ( end_date_time -
DATETIMEVALUE( "1900-01-08 17:00:00") ) / 7) +
MIN( 5,
FLOOR( MOD( end_date_time -
DATETIMEVALUE( "1900-01-08 17:00:00"), 7) / 1) +
MIN( 1, 24 / 8 * ( MOD( end_date_time -
DATETIMEVALUE( "1900-01-08 17:00:00" ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( start_date_time -
DATETIMEVALUE( "1900-01-08 17:00:00") ) / 7) +
MIN( 5,
FLOOR( MOD( start_date_time -
DATETIMEVALUE( "1900-01-08 17:00:00"), 7) / 1) +
MIN( 1, 24 / 8 * ( MOD( start_date_time -
DATETIMEVALUE( "1900-01-08 17:00:00" ), 1) ) )
)
)
), 2 )仕組み:
- この数式は、開始日と終了日の営業時間スコアを計算するための固定履歴アンカーからの経過時間を測定します。次に、開始スコアを終了スコアから減算して差を求めます。
- 数式は、1900-01-08 が月曜日であるため、固定の基準日を使用します。この参照により、週 5 日のクリーンな計算が可能になります。
- 17:00:00 の時刻は始業時間と一致します。この時刻は、太平洋タイムゾーン (UTC-8) の午前 9 時に相当する午後 5 時 (GMT) に対応します。
- 開始時刻またはタイムゾーンを変更するには、タイムゾーンオフセットをローカル開始時刻に加算して、GMT で開始時刻を計算します。たとえば、ニューヨーク (UTC-5) で午前 9 時に開始する場合、計算は 9 + 5 = 14 になります。 数式の
17:00:00を 14:00:00 に置き換えます。詳細は、「数式での日付、日付/時間、および時間値の使用」の日付/時間およびタイムゾーンに関する注意を参照してください。
- 開始時刻またはタイムゾーンを変更するには、タイムゾーンオフセットをローカル開始時刻に加算して、GMT で開始時刻を計算します。たとえば、ニューヨーク (UTC-5) で午前 9 時に開始する場合、計算は 9 + 5 = 14 になります。 数式の
- この数式では、勤務日の長さを 8 時間と定義します。24 / 8 の端数は、営業日の定義に一致するように部分日がスケールされます。
- シフトの長さが異なる場合 (9 時間など)、3 回出現する 8 を適切な数値 (9 など) に変更して数式を調整します。日付文字列の 08 は日付を参照するため、変更しないことが重要です。
MIN( 5, ... )ロジックでは、土曜日と日曜日が無視されます。スコアが 5 日 (金曜日) を超える場合、値は上限に達し、事実上、月曜日までクロックが一時停止します。
