+ Start a Discussion
Del_SantosDel_Santos 

Exclude Holidays and Weekends to compute Opportunity Stage Age

Hi There,

 

Is there a way to calculate the opportunity age per stage excluding holidays and weekends?

 

Considering the following. 

 

-  8am - 5pm during weekdays.

-  Saturday and Sunday is not counted

-  Holidays are not counted.

 

Thanks,

Del

 

Ashish_SFDCAshish_SFDC

Hi Del , 

 

This is the formula to exclude week end, 

 

 

CASE(MOD(TODAY()- DATE(1900, 1, 7), 7), 0, TODAY()+3, 1, TODAY()+3, 2, TODAY()+3,3, TODAY()+5, 4, TODAY()+5, 5, TODAY()+5, 6, TODAY()+4,null)
The above formula can be used to show TODAY()+3 business days [excluding saturday and sunday]. 

 Regards,

Ashish

Ashish_SFDCAshish_SFDC

HI, 

 

This is the formula to calculate business hours, 

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)

 

http://stackoverflow.com/questions/11126378/salesforce-formula-calculate-business-hours-between-two-dates

 

Regards,

Ashish

GlynAGlynA

Del,

 

I'm guessing you'd rather do this with Apex code.  If so, there is a class, "BusinessHours", with which you can compute the difference between two DateTime instances, excluding time outside of business hours (after hours, weekends, holidays).  The BusinessHours static method:

 

public static Long diff(String businessHoursId, Datetime startDate, Datetime endDate)

 

takes the ID of your org's BusinessHours and two DateTime instances and returns the number of milliseconds between the two DateTimes.
 
To get the ID of your org's BusinessHours, you can use a query like this:

 

Id businessHoursId = [SELECT Id FROM BusinessHours WHERE IsDefault = true][0].Id;

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

 
Ashish_SFDCAshish_SFDC

Hi , 

 

Store Holidays using the standard Salesforce Holidays ( Setup | Company Profile | Holidays ).  That will calculate the businesshours. 

 

http://help.salesforce.com/HTViewHelpDoc?id=customizesupport_holidays.htm&language=en_US

 

Regards,

Ashish