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.
