Salesforce 公式:计算两个日期之间的营业时间 [英] Salesforce formula: Calculate business hours between two dates

问题描述

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

I can easily calculate the age of a lead in minutes with a formula like this:

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

但是，我只想计算该时间跨度的营业时间(实际上是分钟)部分.我想在 Salesforce 公式(不是顶点触发器)中执行此操作.我在这里和此处.我对此解决方案的优先事项:

But, I only want to count the business hours (well, minutes actually) portion of that time span. I want to do this in a Salesforce formula (not apex trigger). I have found some inspiration here and here. My priorities for this solution:

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

• Salesforce formula under the 5,000 character limit
• Compare any two datetime fields (not just NOW() vs CreatedDate)
• Reasonable margin of error (+/- 3 hours)
• Ignoring weekends
• Ignoring hours outside 6am-5pm Pacific (13:00-23:59 GMT)
• Ignoring specific holidays like July 4 and Dec 25 (overly complicated for my needs)

只想计算工作日数?这个公式用 873 个编译字符来计算，而 Salesforce Support 建议的模板中有 1,685 个示例(有些人抱怨 超过 5,000 个字符的限制，当他们将较大的模板与其他代码结合使用时).

Want to count just the number of weekdays? This formula does it in 873 compiled characters compared to the 1,685 from the template suggested by Salesforce Support example here (some people have complained about going over the 5,000 character limit when they combine the larger template with other code).

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

阅读我的回答以了解它是如何工作的，以及如果需要，您甚至可以如何计算营业时间.

Read my answer to learn how it works and how you can even calculate business hours if you need it.

推荐答案

我解决了这个问题，方法是从一个参考点计算两个不同日期的工作日两次，然后减去以获得之间的工作日数，包括小数部分，所以我可以轻松地将其转换为营业时间或营业时间.通过将参考点选择为过去的星期一(例如 1996 年 1 月 1 日)开始营业，我可以更轻松地计算数学并解决 Salesforce 公式的局限性.

I solved this by calculating business days from a reference point twice for two different dates and then subtracting to get the number of business days between, including the fractional portion so I can convert it to business hours or business minutes easily. By choosing the reference point as start of business on a past Monday, say January 1, 1996, I can make the math much easier and work around the limitations of Salesforce formulas.

例如，假设我有两个日期，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 个工作日的正确答案.

As an example, say I have two dates, D1 = "5/10/2012 12:49 PM" and D2 = "6/20/2012 14:19 PM." My work week is 5 days/week x 10 hours/day. I arbitrarily choose a reference date of "2012-04-02 13:00:00." Counting from the reference date, there are 28.682 business days to D1 and 57.832 business days to D2. Subtracting gives me the correct answer of 29.150 business days between D2 and D1.

以下公式用于计算任何 Salesforce 记录的营业时间(从格林威治标准时间 1300 开始的 11 小时天):

Here is a formula that calculates the age of any Salesforce record in business hours (11 hour days starting at 1300 GMT):

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)

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

Let's break it down. I'm calculating two business day timespans. For each I'm counting the number of full business weeks, full business days within the last partial week, and fractional business hours within the last partial day then adding it all up.

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

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

Counts the number of full weeks since the reference point (must be a Monday) and credits 5 business days for each.

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

计算最后一周中额外的全天数，并为每个工作日计入 1 天.

Counts the number of extra full days in the last partial week and credits 1 day for each.

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

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

Calculates the portion of a day that has elapsed since the start of business. Modulus 1 gives just the decimal portion back. Multiplying by 24/11 converts this from a fractional 24-hour day into a fractional 11-hour business day (could have used 8 instead of 11 for an 8-hour day).

MIN(1, ...)

确保我们不会为很容易发生在深夜的零碎部分计入超过一个完整的工作日.

Makes sure we never credit more than one full business day for the fractional piece which could easily happen late in the evening.

MIN(5, ...)

确保我们不会为周六或周日很容易发生的部分工作周计入超过 5 个完整工作日.

Makes sure we never credit more than 5 full business days for a partial work week which could easily happen on a Saturday or Sunday.

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

将其从工作日转换为整个工作时间.将其保留到工作日，包括小数部分.

Converts this from business days into whole business hours. Leave it off for business days including the fractional portion.

结束语:

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

这篇关于Salesforce 公式:计算两个日期之间的营业时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！