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.

  • Time zone issue? salesforce.stackexchange.com/questions/122604/… – Adrian Larson Jul 14 '16 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 '16 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 '16 at 20:37
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.