Simplify how Business hour calculations can be created - Ideas - Salesforce Trailblazer Community
Trailblazer Community

All Ideas

Idea Details

Post an Idea
90  Points
Open
Idea has been posted. Give it an upvote or downvote.

Simplify how Business hour calculations can be created

Administration

After battling with formulas to calculate the difference in 'business hours' between 2 date/time fields, it became apparent that there needs to be something simple for this, after getting it 90% there it seems that there are flaws with formulas where it gives a negative value if the dates fall on the same day, but one is outside business hours. 
User-added image
This occurred using the below formula - 
ROUND( 12 * (
   ( 5 * FLOOR( ( DATEVALUE(  Replied_Date_Time__c  ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 12 * ( MOD( Replied_Date_Time__c  - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  Received_Date_Time__c  ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( Received_Date_Time__c   ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 12 * ( MOD( Received_Date_Time__c   - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
    )
   ) 
  ), 
2 )
+
ROUND( 9 * (
   (  FLOOR( ( DATEVALUE(  Replied_Date_Time__c  ) - DATE( 1900, 1, 6) ) / 7) +
    MIN(1, 
     MOD( DATEVALUE( Replied_Date_Time__c   ) - DATE( 1900, 1, 6), 7) +
     MIN( 1, 24 / 9 * ( MOD( Replied_Date_Time__c  - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1 ) ) )
    ) 
   )
 -
   (  FLOOR( ( DATEVALUE(  Received_Date_Time__c  ) - DATE( 1900, 1, 6) ) / 7) +
     MIN( 1,
      MOD( DATEVALUE( Received_Date_Time__c   ) - DATE( 1900, 1, 6), 7 ) +
      MIN( 1, 24 / 9 * ( MOD( Received_Date_Time__c   - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1) ) )
    )
   ) 
  ), 
2)


You can enter Business hours & holidays within the set up and yet these cannot be referenced in formulas. 

It would be a really good thing if you could simply use a basic 'BUSINESS HOURS' command within a formula. Especially for a business such as ours where we have different hours at weekends and don't open bank holidays. 

 

Merge Idea · Flag

  • Upvotes
  • Downvotes

Ideas

Apps

from AppExchange

Questions

No results found.

Help us to keep IdeaExchange clean by pointing out overlapping ideas. We'll investigate your suggestion and merge the ideas if it makes sense.



 

 

Thanks for your merge suggestion. We will review it shortly and merge the ideas if applicable.

Salesforce takes abuse situations very seriously. Examples of abuse include but are not limited to posting of offensive language or fraudulent statements. To help us process your request as quickly as possible, please fill out the form below describing the situation. For privacy and security reasons, the final outcome of an abuse case may not be revealed to the person who reported it.


 

Thank you for your feedback. We take abuse seriously and will investigate this issue and take appropriate action.