Salesforce formula: Calculate business hours betwe

Question

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

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

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:

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

---

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.

Answer 1

I know you were looking for business hours, however I noticed you have a number of business days formula, and thought I'd post this alternative number of Work days (Monday - Friday) between two dates:

1 +
(
 (EndDate__c - StartDate__c) * 5 -
 (MOD(StartDate__c - DATE(1970,1,4),7) - MOD(EndDate__c - DATE(1970,1,4),7)) * 2
) / 7 -
IF(MOD(EndDate__c - DATE(1970,1,4),7) = 6,1,0) -
IF(MOD(StartDate__c - DATE(1970,1,4),7) = 0,1,0)

Adapted from this c# formula. Compiles in 479 characters.

In theory you could then throw a calculation for the hour on top of this to work out the number of hours of course.

Answer 2

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.

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.

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)

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)

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

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, ...)

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.

Closing thoughts:

• This formula counts business holidays as working days. I can live with that.
• Probably an hour off when our timespan overlaps a daylight savings time shift. I can live with that.
• Since we're not counting months or years I think we're immune to leap year issues.
• I used DATETIMEVALUE() as little as possible because it overly inflates the size of the formula.
• The above formula is 1,099 characters which should leave me enough room to use a different reference date for EMEA records.
• I'm not going to use this with any dates before 1/1/1996, but it should work fine if you pick an even older Monday.

Answer 3

Just a note regarding daylight savings time (arrg)... When it's critical to account for it, I do an IF statement where if my source field (eg. CreatedDate) is between the daylight savings time range, I use one (adjusted) version of the formula, and if it's not, I use a non-adjusted version. Doubles the size of the formula, but is fairly simple since the two versions are only different by a character or two.

Answer 4

If you can tolerate a reaswonable error of margin

(count the number of workdays since createdBy date -1) * (11 hours) +(time_now_in_24_hr_format - 6 )