+ Start a Discussion
Stefano AmentaStefano Amenta 

Formula for specific business hours between two dates

Hi,
Currently, I have been using the formula below to calculate the business hours between two dates.

I have a question, though:
  • If I want to calculate the business hours for a specific timeframe such as from 9am to 6pm (Monday to Friday, China time) would this formula still be valid?

Thanks in advance for the help.
 
CASE(MOD( In_Accept_Stage_Date_Track__c - DATE(1985,6,24),7), 0 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( Today() - In_Accept_Stage_Date_Track__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( Today() - In_Accept_Stage_Date_Track__c )/7)*5)

 
JayantJayant
The only sure shot way of calculating age precisely in business hours is using the BusinessHours class in Apex - 
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_businesshours.htm

As far as the formula is concerned, here is its documentation - 
https://help.salesforce.com/articleView?id=000089863&type=1
NagendraNagendra (Salesforce Developers) 
Hi Stefano,

Sorry for this issue you are facing.

Business hours calculation work based on your defined business hours with timezone and also Salesforce automatically calculates daylight savings times for the time zones available for business hours, so you don’t have to configure rules to account for time zones.

Time will reflect as per the user timezone.
  • https://help.salesforce.com/articleView?id=customize_supporthours_guidelines.htm&type=5
  • https://help.salesforce.com/articleView?id=customize_supporthours.htm&type=5
Thanks,
Nagendra