1

I have created a formula field to calculate the number of Business Days Since Last Activity. Below is the Formula I currently have:

CASE(MOD( LastActivityDate - DATE(1985,6,24),7), 
0 , CASE( MOD( Today() - LastActivityDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( Today() - LastActivityDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( Today() - LastActivityDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( Today() - LastActivityDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( Today() - LastActivityDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( Today() - LastActivityDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( Today() - LastActivityDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( Today() - LastActivityDate )/7)*5)

The issue that I am having is that this does not seem to be calculating properly for a few of the values. Listed below is the values I am receiving from a report based on today being Thursday. In the format of how many days in the past the activity was - what I should be getting from the formula - the number I am receiving from the formula field.

  • 0 - 0 - 1
  • 1 - 1 - 2
  • 2 - 2 - 3
  • 3 - 3 - 4
  • 4 - 3 - 4 ( This is because 4 days ago was a Sunday. Not a business day.)
  • 5 - 3 - 4 ( This was Saturday which is still only 3 Bdays ago.)
  • 6 - 4 - 5
  • 7 - 5 - 6

I hope this makes sense, what seems to be happening is it is adding an extra day for 1 - 3 and then 4 - 7 needs to have one missing. Please let me know if you need anymore clarification.

3
  • Time zone issue? salesforce.stackexchange.com/questions/122604/…
    – Adrian Larson
    Jul 14, 2016 at 18:35
  • Yeah you are probably right. The instance I am in has a time zone of (GMT=01.00)British Summer Time (Europe/London) but the records that this will affect have all been created in the Eastern American time zone.
    – cmmoutes13
    Jul 14, 2016 at 18:42
  • Probably important to note that this formula gives you weekdays, and not strictly business days (which would additionally take holidays into account).
    – Derek F
    Jul 14, 2016 at 20:37

1 Answer 1

3

I figured out the issue, in the following link there is a section that states that the formula will include the start and end date. And that is not the case for me. I did not want the end date to be included. So I simply added a minus one to the end of the formula and that seems to have solved the issue.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .