# Help with Business Day Formula

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

## 1 Answer

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.