範例日期公式

當您使用此主題中的範例公式時,請記住這些考量事項。

• 若要避免複製公式時發生語法錯誤,請取代:

  • 具有實際 DATE 值的 日期預留位置,例如「日期」欄位,例如 CloseDate 或 TODAY() 函數。

  • 具有實際「日期/時間」值的 date_time 預留位置,例如 CreatedDate 或 NOW()。

  • 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 ))。

查出該年是否為閏年

此公式會判斷某年的年份是否為高年份 (亦即其有 366 天)。此邏輯對於任何需要每日精確度的計算而言至關重要,從計算每日財務利息到追蹤藥物劑量。例如,若每年將年度金額除以 365 時,便會導致每日資料不準確。透過此公式,您可以根據年度,將計算分數動態設定為 365 或 366。

OR( 
    MOD( YEAR( date ), 400 ) = 0, 
    AND( 
        MOD( YEAR( date ), 4 ) = 0,
            MOD( YEAR( date ), 100 ) != 0
  )
)

運作方式:

• 當年份符合下列兩個特定條件的其中一項時,該年份即為逾期年份。一種條件是該年份完全可除以 400 (例如,年份 2000)。另一個是可以除以 4,但不能除以 100 (例如 2024 是高年份,但 1900 不是)。
• MOD() 函數會計算分部的餘數。如果為 MOD( Year, 400 ) = 0,則表示該年份可除以 400 且沒有餘數。
• OR() 函數會換行整個公式。如果兩個條件皆為 true,則公式會傳回 TRUE。

查出日期的季度

決定特定活動所屬的會計季度對於如銷售預測、財務報告或績效分析等活動而言至關重要。使用公式可計算標準年度、調整後會計年度的季度 (1–4),甚至可將日期與目前季度進行比較。

標準會計年度:如果第 1 季在 1 月開始,請使用此公式。

CEILING( MONTH ( date ) / 3 )

排班會計年度:如果您的會計年度在一月以外的月份開始,請使用此公式。調整 ADDMONTHS() 中的 -1 值,以符合重新排班所需的月數,以符合 1 月。例如,如下所示的公式會在 2 月開始時返回 1 個月。

CEILING( MONTH ( ADDMONTHS ( date, -1 ) ) / 3)

檢查「日期」是否在目前 (標準) 季度:如果日期在與今天相同的標準季度和年份中,請使用此公式傳回 TRUE。此選項對於驗證目前季度交易或防止對過去記錄進行編輯等情況很實用。

AND(
    CEILING( MONTH( date ) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ),
    YEAR( date ) = YEAR( TODAY() )
)

運作方式:

• 這些公式中的邏輯會將月數 (1–12) 除以 3 (季度中的月數)。

• CEILING() 函數會將結果向上捨入至最接近的整數。例如：2 月 (2 月) 除以 3 為 0.66,進而捨入為 1 (季)。4 月 (月 4) 除以 3 為 1.33,這會捨入為 2 (季)。

• 針對轉換的會計年度,ADDMONTHS() 幾乎會將日期切換回來,讓計算符合 1 月的開始。例如,若減去 1 個月,則為了公式,二月會變成一月 (週期開始時間)。

• 檢查日期是否在目前季度時,公式會確認季度與年度皆符合 TODAY(),以確保不會錯誤地將上一季的日期標記為目前。

查出日期是第幾週

依週分組記錄是如每週銷售報告、供應鏈規劃或資源使用追蹤等活動的基本需求。由於不同組織的第 1 週定義方式不同,因此 Salesforce 提供兩種方法:ISO 標準 (最適用於業務報告) 和絕對計數 (最適用於簡單且本地化的年度進度追蹤)。

ISO 標準週別 (建議):使用此公式可使大多數業務報告符合 ISO-8601 標準。週一一始於週一,且包含週四的第一週會視為週 1。

ISOWEEK( date )

簡單絕對週數:如果您的組織將第 1 週定義為一律從 1 月 1 日開始,則使用此公式,無論星期幾為何。此方法對於簡單的年度計數器很實用,您只要想知道自年度開始以來已經過多少 7 天區塊。

CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)

運作方式:

• ISO 標準邏輯:ISOWEEK() 函數是原生工具,會根據 ISO-8601 定義自動計算週數 (1–53)。它會自動處理 1 月在上一年度的最後一週開始,或 12 月在下一年度第一週結束的邊緣個案。
• 絕對計數邏輯:
  • 公式 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。

查出兩個日期是否在同一個月份

針對如佣金計算、行銷活動歸屬或合併帳單等活動,通常需要判斷兩個事件是否在相同的會計或報告期間發生。此公式會檢查兩個特定日期欄位 (例如,Opportunity CloseDate 和自訂 Invoice_Date__c) 是否在相同的行事曆月份內。

AND(
    MONTH( date_1 ) = MONTH( date_2 ),
    YEAR( date_1 ) = YEAR( date_2 )
)

您也可以動態套用此邏輯,以驗證日期是否與今天相對於的月份。此方法適用於條件式格式化,例如醒目提示本月到期的工作,或為自動更新的「本月」報告建立公式標記。

AND(
    MONTH( date ) = MONTH( TODAY() ),
    YEAR( date ) = YEAR( TODAY() )
)

運作方式:

• 公式會使用 MONTH() 來針對這兩個日期解壓縮數字 (1–12) 並進行比較。

• 它也會使用 YEAR() 來確保年份相符。若沒有此步驟,則 2024 年 1 月的日期不正確符合 2023 年 1 月的日期。

• AND() 函數需要月份和年份皆相同,才能傳回 TRUE。

查出月份的最後一天

計算月份的最後一天對於財務與契約管理使用個案而言至關重要。常見使用個案包括設定動態訂閱結束日期、決定發票到期日期,或報告在月份結束時結束的交易。此公式可透過從下個月的第一天減去一天,藉此簡化處理 28 日、30 日和 31 日的月份,以及高年。

ADDMONTHS( DATE( YEAR( date ), MONTH( date ), 1 ), 1 ) - 1

運作方式:

• 公式 DATE( YEAR( date ), MONTH( date ), 1 ) 會從原始日期欄位建立設為月份第一天的日期。
• ADDMONTHS( ..., 1 ) 會將「月份的第 1 天」日期正確移到一個月前。例如,將 1 月 1 日變更為 2 月 1 日。
• 從下個月的第一天減去 1 時,會自動將日期回復為本月的最後有效日。此轉換會自動正確處理 2 月 28/29 日和 30/31 日的月份。

將月份顯示成字串而非數字

雖然標準日期欄位會以數字顯示,例如 01/15/2024,但報告和面向客戶的通訊通常需要更精簡的格式。此公式會將月數值轉換為其完整文字名稱,例如 1 月。此轉換對於在如報價文件、電子郵件範本或行銷電子報等資源中產生專業標題 (例如「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",
    ""
)

如果您的組織使用多種語言,請以可翻譯的自訂標籤取代月份名稱。透過此方法,月份名稱會根據使用者的語言設定動態更新。例如,針對美國使用者顯示「一月」,針對法國使用者顯示「一月」。

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,則傳回第二個值,依此類推。
• 最後行 ("") 提供預設值。如果日期欄位為空白,則 CASE() 陳述式會移至此行並傳回空白文字字串,以確保不會發生錯誤。

查出並顯示日期之中的星期幾

雖然 Salesforce 以數字方式儲存日期 (例如 2024-12-25),但外部通訊和內部排程通常需要更容易人類閱讀的格式。此公式會從日期欄位中識別一週的特定日期,並將其轉換為文字 (例如「週三」)。此函數對於電子郵件行銷範本很實用,您想要使用如「快樂週五!」這樣的方式來個人化問候。另一個使用個案範例為員工報告,因此您可以輕鬆找到並篩選週末活動。

CASE(
    WEEKDAY ( date ),
    1, "Sunday",
    2, "Monday",
    3, "Tuesday",
    4, "Wednesday",
    5, "Thursday",
    6, "Friday", 
    7, "Saturday",
    ""
)

如果您的組織使用多種語言,請以可翻譯的自訂標籤取代日期名稱。透過此方法,日期名稱會根據使用者的語言設定動態變更 (例如為西班牙文使用者顯示「Domingo」)。

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,則會傳回第一個值 (「星期日」或 $Label.Day_of_Week_1)。如果找到 2,則會傳回第二個值,依此類推。
• 最後行 ("") 提供預設值。如果日期欄位為空白,則 CASE() 陳述式會流失至此行,並傳回空白文字字串,而非錯誤。

查出日期後的下一個星期幾

計算「下週一」或「即將到來的星期五」的特定日期,對於設定一致的每週排程十分重要。例如,或許您的支援小組會每週五執行品質稽核,或帳單週期會在週一開始。無論記錄的建立日期為何,此公式都會自動計算正確的即將到來的日期。

date + ( target_day - WEEKDAY( date ) +

IF( target_day <= WEEKDAY( date ), 7, 0 ) )

將 target_day 取代為與您想要的日期對應的數字:1=星期日、2=星期一、3=星期二、4=星期三、5=星期四、6=星期五、7=星期六。

運作方式:

• 此公式會計算 target_day (您想要的日期) 和 WEEKDAY( date ) (記錄的目前日期) 之間的間隙。
• IF() 陳述式會判斷目標日期是否晚於本週,或將結束到下週。
  • 本週稍後:如果今天是週一 (2),而您想要週五 (6),則數學只需要 6 - 2 = 4 天。IF 新增 0。
  • 過去或今日:如果今天是星期五 (6) 且您想要星期一 (2),則數學為 2 – 6 = –4 (表示星期一是 4 天前)。由於目標今天為「小於或等於」,因此 IF 陳述式會在結果中加上 7:–4 + 7 = 3 天後 (下週一)。

查出兩個日期之間的天數

若要測量效率,例如追蹤「銷售週期」時間 (結束日期減去建立日期) 或「個案解決時間」,請計算兩個特定事件之間的持續時間。這些公式會計算經過的精確天數,或自動標記超過特定時限的記錄。

基本持續時間計算:若要查出兩個特定日期之間的簡單天數,請從較晚的日期減去較早的日期。

later_date — earlier_date

檢查「日期是否在超過 30 天前發生」:您也可以使用日期數學來標記已經過時或逾期的記錄。例如,使用此公式可識別一個月內沒有活動的商機,或已逾期的發票。如果與今日相對於的日期超過 30 天,則傳回 TRUE。

( TODAY() - date ) > 30

運作方式:

• Salesforce 會在內部將日期儲存為簡單數字 (整數)。當您將某個日期減去另一個日期 (例如,later_date - earlier_date) 時,Salesforce 會計算這些整數之間的簡單差異,以天數傳回結果。
• 在第二個公式中,TODAY() - date 會從目前系統日期減去欄位的日期,以計算記錄的帳齡 (天)。
• > 30 運算子會將該年齡與您的值進行比較。如果差值為 31 天或更高,則公式會傳回 TRUE。否則會傳回 FALSE。

查出兩個日期之間的工作日數

針對活動,例如追蹤解決支援個案或結束銷售機會的工作日數,請計算兩個日期之間的工作日數 (工作日)。與簡單的日期減法不同,此公式可確保您僅計算星期一到星期五。

(
    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,以為每個完整週的五個工作日信用額。
• MOD() 函數會查看計入完整週後的剩餘天數 (0–6)。MIN( 5, ... ) 函數會將部分週的工作日計數縮小為 5,因此週六與週日不會在週五後增加總工作日數。

備註 此公式適用於標準 5 天工作週 (星期一到星期五)。針對其他排程 (例如 6 天一週或包含週末的排班) 進行調整,需要調整參考日期和數學邏輯,這是本主題未涵蓋的進階自訂。

查出兩個日期之間的月數

如訂閱管理或契約續約等活動通常需要計算兩個日期之間的行事曆月數。例如,您可能正在決定多年契約的帳單持續時間或計算員工權益的持續時間。此公式會根據月份和年度值尋找差異,忽略月份的特定日期。

( ( YEAR( later_date ) - YEAR( earlier_date ) ) * 12 ) + (MONTH ( later_date ) - MONTH( earlier_date ) )

運作方式:

• ( YEAR( later_date ) - YEAR( earlier_date ) ) * 12 會計算兩個日期之間的年差,並將其乘以 12,以將其轉換為基準月份數。
• MONTH( later_date ) - MONTH( earlier_date ) 會計算月份數字 (1–12) 之間的差異。
• 最後,公式會將這兩個值相加在一起。

備註 此計算會嚴格考量行事曆月份。例如,1 月 31 日的開始日期和 2 月 1 日的結束日期會傳回 1 個月,因為月整數增加 1 個月,即使僅經過一天。

在日期中加入年、月、日

計算未來日期是工作流程自動化的重要需求。這些公式會動態地將日期投影到未來。例如,將其用於活動,例如設定通話後 5 天的後續工作期限、決定契約到期日期,或計算員工的委託排程。

新增天數:若要排程短期後續追蹤或期限,請將天數直接新增至 日期欄位。Salesforce 會將日期視為整數,因此新增 5 會將日期增加 5 天。

date + 5

新增月數:針對如每季檢閱或訂閱續約等中期排程,請使用 ADDMONTHS()。此函數比使用簡單新增更有智慧,因為它會自動處理不同長度的月數 (28, 30, 31 天)。

ADDMONTHS( date, 1 )

備註 如果您將 1 個月加到 1 月 31,則結果不是 2 月 31 日 (不存在)。此函數會自動快取至月份的最後一個有效日期,在此個案中為 2 月 28 日 (或年份中的 29 日)。

新增年數:將此公式用於計算,例如決定帳單的多年契約持續時間或計算員工權益的持續時間。透過將年數乘以 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 日。

運作方式:

• date + 5 公式只會增加日期的整數值。
• ADDMONTHS( date, ... ) 公式會新增行事曆月份。將年數乘以 12 (12 * num_years) 會將年數轉換為月數,讓函數可以處理年數。
• IF() 陳述式檢查兩個條件:原始日為 29 日,計算的未來日為 28 日。如果兩者皆為 true,則表示系統已針對非跳轉年度進行調整。接著公式會新增 1 天,將日期推送至 3 月 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 個行事曆日。

查出日期/時間的時、分或秒

從「日期/時間」欄位提取特定時間元件對於細微的報告和排程而言十分重要。某些範例包括標記營業時間之後建立的支援個案,或依照當天小時分析通話量模式,以最佳化員工。這些公式會區隔時間戳記的確切時、分或秒。

重要 Salesforce 會以 GMT (格林威治標準時間) 儲存所有「日期/時間」值。若要取得準確的當地時間,請以您時區差異的特定數值計算取代公式的 TZoffset 預留位置。

• 如果您落後於 GMT,請以您落後的小時數除以 24 取代 TZoffset。例如,針對東部時間 (UTC-5),請使用 5.0 / 24 的值。

• 如果您早於 GMT,請以加號 (+) 變更公式中的減號 (–)。然後將 TZoffset 取代為您預先的小時數除以 24。例如,針對新加坡 (UTC+8),請使用 8.0 / 24 的值。

如需詳細資訊,請參閱在公式中使用日期、日期/時間和時間值中的「日期/時間」和時區相關備註。

查出「小時:您可以使用這些公式將小時以數字的方式解壓。此方法是「當日時間」分析的主要方法。

• 24 小時格式 (0–23):最適用於計算和排序。

  HOUR( TIMEVALUE( date_time - TZoffset ) )

• 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 )
  )
  

尋找分鐘和秒:使用這些公式可將分鐘或秒 (0–59) 解壓縮。此方法適用於計算精確的持續時間,例如「第一次回應的時間」。

• 分鐘數:

  MINUTE( TIMEVALUE( date_time - TZoffset ) )

• 秒:

  SECOND( TIMEVALUE( date_time - TZoffset ) )

判斷 AM 或 PM:使用此公式傳回 AM 或 PM 作為文字字串。建立資源的自訂 12 小時時間戳記 (例如電子郵件範本或報告) 時,此輸出很有用。

IF( 
    HOUR( TIMEVALUE( date_time - TZoffset ) ) < 12, 
    "AM", 
    "PM" 
)

以字串顯示「完整」 (HH:MM:SS AM/PM):當您想要以精簡且人類可讀的格式 (例如上午 09:05:30) 顯示時間時,請使用此複合公式。它會結合此區段中其他公式的邏輯,並使用 LPAD() 來確保小時、分鐘和秒值一律顯示為兩位數 (例如 09 而非 9)。

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 AM) 的邊緣個案,以 12 而非 0 表示。
• LPAD(..., 2, "0") 會使用前置零的單一數字鍵,以確保時間看起來標準 (例如 09:05 而非 9:5)。

查出日期/時間之間經過的時間

有效追蹤需要精確測量工作流程的持續時間。例如,確定支援個案保持開啟的時間長度 (最小至一分鐘) 可協助強制執行服務層級契約 (SLA)。另一個範例是透過追蹤商機建立與第一個銷售活動之間的時間延遲來醒目提示效率差異。這些公式會將此持續期間計算為簡單數字或人類可讀的文字字串。

簡單日數:如果您要計算全天數的差異,只要將較早的日期減去較晚的日期即可。此方法會傳回數字。例如,1.5 代表 1 天和 12 小時)。

later_date  -  earlier_date

經過的格式化時間字串 (天、小時、分鐘):若要以如「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 ) 會取用總差異,並隔離整數 (整數) 以計算完整天數。
• MOD( (diff) * 24, 24 ) 會取用剩餘的小數位數 (部分日),將其乘以 24,以將其轉換為小時,並隔離未組成完整日的小時。
• MOD( (diff) * 24 * 60, 60 ) 會將差乘以 1440 (一天中的分鐘數),以取得分鐘總數,然後使用 MOD 尋找移除完整小時後的剩餘分鐘數。小時。
• 最終 FLOOR() 會將分鐘值保持為整數,並防止分鐘元件捨入至 60。

查出兩個日期/時間之間的工作時數

準確追蹤服務層級契約 (SLA) 通常需要以「營業時間」來測量持續時間,而非原始時鐘時間。例如,考量優先順序較高的支援個案,其抵達時間為週五下午 4:00,且在週一上午 10:00 解決。實際經過的時間為 66 小時,但假設排程為 9 到 5,則業務持續時間僅為 2 小時。此公式會計算該特定業務持續時間,並自動排除週末和非工作夜晚時間。

備註 公式會假設 8 小時的工作日為上午 9:00 到下午 5:00,且使用者在 UTC-8 (太平洋標準時間) 區域中工作。它也會假設固定偏移,且不會考慮「日光節約時間」(DST) 變更、特定假日或您組織設定的營業時間。

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 會與工作日開始一致。此時間對應於下午 5:00 GMT,其等同於太平洋時區 (UTC-8) 的上午 9:00 開始時間。
  • 若要變更開始時間或時區,請將時區位移新增至當地開始時間,以 GMT 計算開始時間。例如,如果您位於紐約 (UTC-5) 並在上午 9:00 開始,則數學值為 9 + 5 = 14。將公式中的 17:00:00 取代為 14:00:00。如需詳細資訊,請參閱在公式中使用日期、日期/時間和時間值中的日期/時間和時區注意事項。
• 公式會將工作日長度定義為 8 小時。24 / 8 分數會將部分日期分級為符合工作日定義。
  • 如果您的排班長度不同,例如 9 小時,請將 8 的三個發生次數變更為適當的數字 (例如 9)。請務必不要變更日期字串中的 08,因為該數字是指月份幾號。
• MIN( 5, ... ) 邏輯會忽略週六與週日。如果分數超過 5 天 (星期五),則會超過值,有效暫停時間直到星期一。