Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Free

Calculate Working days through Apex

Hi all,

sorry if this has already been posted.

I am working on the Customer Service part of Salesforce.
When logging Cases, the system should calculate a due date based on the SLA and on the Customer Service Center Calendar (meaning, without holidays, bank holidays, week-ends, etc.).
Since there are no standard feature to enable this feature, I have been told this is feasible through Apex.

I'm pretty new in the Apex world: I've done some developments, but nothing too fancy, so if you could help me out on this one, I would really appreciate.
Not mentioning I'm sure it's the kind of issue Customers face all the time...

Free

MikeGoelzer
Hi Free,

Have you looked at Salesforce Labs's Entitlements 2.0?  It does exactly what you're describing, and its open source, so you could add on to it if you're so inclined.

(Confession:  I have a biased perspective as my company built this and the previous version under contract for Salesforce.  But the problem it solves really does sound like the ones you're describing.)

http://www.salesforce.com/appexchange/detail_overview.jsp?NavCode__c=&id=a0330000005lOz7AAE

Mike Goelzer
miss v
has anyone found a way to make this happen? i would like to have a field in my system that generates a count of only working or business days.
Blanka

Hi Miss V,

I have worked on a formula and it seems to be working for me. In the case below I am asking the system to calculate the number of business days between two date - July 1 ,2009 ( this is my strat date) and September 30,2009 ( this is my end date)

IF(AND((5 - (CASE(MOD( Start_Date__c - DATE(2009, 7, 1), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( End_Date__c - DATE(2009, 9, 30), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c - Start_Date__c ) + 1) < 7)),
((CASE(MOD( End_Date__c - DATE(2009, 9, 30), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD( Start_Date__c - DATE(2009, 7, 1), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c - Start_Date__c ) - (CASE(MOD( Start_Date__c - DATE(2009, 7, 1), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD( Start_Date__c - DATE(2009, 7, 1), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD( End_Date__c - DATE(2009, 9, 30), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))

Rup3

That's rather complex.

Something much simpler works for me :

EndDate__c - StartDate__c - 2*( floor((EndDate__c - DATE(2009, 9, 14))/7) - floor((StartDate__c - DATE(2009, 9, 14))/7) )

HTH,

Rup

BrianO'Halloran

This Formula takes in account when the day of the week for day2 is less then the day of the week for day one.

IE.   Day1 =  Friday 11/6/2009   -  Day 2 =   Tuesday 11/10/2009  :  = 2 Workdays

Formula Field

IF(
( MOD(Day2- DATE(1900, 1, 7), 7)  < MOD(Day1- DATE(1900, 1, 7), 7)  ) ,
(Day2 -  Day1  - (2*(floor((Day2 -  Day1 )/7))))-2,
(Day2 -  Day1  -( 2*(floor((Day2-  Day1 )/7))))
)

Enth

Err, that's going to return -1 if day1 is a Sat and day2 is a Sun...

Little more work

Mahesh.Nalla

thanks Rup3. very helpful