Salesforce 公式 : Calculate business hours between two dates

Question

我可以使用如下公式轻松计算出潜在客户的年龄(以分钟为单位):

ROUND((NOW()-CreatedDate)*1440,0)

但是，我只想计算该时间跨度的营业时间(实际上是分钟)部分。我想在 Salesforce 公式(不是顶点触发器)中执行此操作。我找到了一些灵​​感 here和 here .我对此解决方案的优先考虑:

• 5,000 个字符限制内的 Salesforce 公式
• 比较任意两个日期时间字段(不仅仅是 NOW() 与 CreatedDate)
• 合理的误差范围(+/- 3 小时)
• 忽略周末
• 忽略太平洋时间早上 6 点到下午 5 点以外的时间(格林威治标准时间 13:00-23:59)
• 忽略 7 月 4 日和 12 月 25 日等特定假期 (对我的需求而言过于复杂)

---

只想计算工作日数？这个公式在 873 个编译字符中计算，而 Salesforce 支持人员建议的模板中有 1,685 个字符 example here (有些人在将较大的模板与其他代码组合时提示 going over the 5,000 character limit)。

(
    5*FLOOR((TODAY()-DATE(1996,01,01))/7) + 
    MIN(5, MOD(TODAY()-DATE(1996,01,01), 7))
) - (
    5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) + 
    MIN(5, MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7))
)

阅读我的回答以了解其工作原理以及如何在需要时计算营业时间。

Answer 1

我解决了这个问题，方法是从一个引用点计算两个不同日期的工作日两次，然后减去以获得两者之间的工作日数，包括小数部分，以便我可以轻松地将其转换为工作时间或工作分钟。通过选择引用点作为过去星期一的业务开始时间，比如 1996 年 1 月 1 日，我可以简化数学计算并解决 Salesforce 公式的局限性。

例如，假设我有两个日期，D1 =“5/10/2012 12:49 PM”和 D2 =“6/20/2012 14:19 PM”。我的工作周是 5 天/周 x 10 小时/天。我随意选择“2012-04-02 13:00:00”作为引用日期。从基准日算起，到D1有28.682个工作日，到D2有57.832个工作日。减去 D2 和 D1 之间的正确答案是 29.150 个工作日。

这是一个计算任何 Salesforce 记录在营业时间(从格林威治标准时间 1300 开始的 11 小时工作日)中的年龄的公式:

ROUND(11*(
(5*FLOOR((TODAY()-DATE(1996,01,01))/7) +
MIN(5, 
    MOD(TODAY()-DATE(1996,01,01), 7) +
    MIN(1, 24/11*(MOD(NOW()-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) +
MIN(5, 
    MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7) +
    MIN(1, 24/11*(MOD(CreatedDate-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
), 0)

让我们分解一下。我正在计算两个工作日的时间跨度。对于每一个，我都会计算完整工作周的数量、最后部分周内的完整工作日数以及最后部分日内的部分工作时间，然后将它们全部相加。

(5*FLOOR((TODAY()-DATE(1996,01,01))/7)

计算自引用点(必须是星期一)以来的完整周数，并为每个计算 5 个工作日。

MOD(TODAY()-DATE(1996,01,01), 7)

计算最后一周的额外全天数，每天计入 1 天。

24/11*(MOD(CreatedDate-DATETIMEVALUE('1996-01-01 13:00:00'), 1))

计算自业务开始以来已经过去的那一天的部分。模数 1 只返回小数部分。乘以 24/11 会将其从 24 小时的小数日转换为 11 小时的小数工作日(对于 8 小时的一天，可以使用 8 而不是 11)。

MIN(1, ...)

确保我们绝不会将超过一个完整工作日的小数部分计入积分，这很容易在深夜发生。

MIN(5, ...)

确保我们不会将超过 5 个完整工作日计入部分工作周，这很容易发生在周六或周日。

ROUND(11*(...), 0)

将其从工作日转换为整个工作时间。在工作日内将其关闭，包括小数部分。

结束语:

• 此公式将营业假期计为工作日。我可以接受。
• 当我们的时间跨度与夏令时转变重叠时，可能会休息一个小时。我可以接受。
• 由于我们不计算月份或年份，我认为我们不会受到闰年问题的影响。
• 我尽可能少地使用 DATETIMEVALUE()，因为它过度膨胀了公式的大小。
• 上面的公式是 1,099 个字符，这应该给我留出足够的空间来为 EMEA 记录使用不同的引用日期。
• 我不会将此用于 1996 年 1 月 1 日之前的任何日期，但如果您选择更早的星期一，它应该可以正常工作。