Negative number returning for Business hours calculation - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Cat SheltonCat Shelton 

Negative number returning for Business hours calculation

Hey all, 

I have a Formula which calculates how long, in business hours, it takes for a reply to an email to happen. The formula works great within business hours, but if an email comes in BEFORE business hours (for example - 6am in the morning) and is replied to within business hours on the same day (example - 10am) then it returns a negative number. See below - 

User-added image

The formula is - 
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)
If anyone can help stop this it would be appreciated :) 
Best Answer chosen by Cat Shelton
Cat SheltonCat Shelton
https://www.professorflow.com/post/salesforce-business-hours - I was on a call with our consultant and he recommended this, it installs an Apex which can be referenced within a Flow. This works amazingly! 

All Answers

Matt SmithMatt Smith
Hi Cat,

As far as I remember this is a commonly encountered issue with the "standard" business hours formula.

Unfortunately I am nowhere near experienced enough to write a corrected formula for you, but I did find a very comprehensive thread that may help you, depending on your scenarios and if you have the capability for someone to write apex for you:

https://trailblazers.salesforce.com/answers?id=9063A000000iXTlQAM

If you scroll down to the comments from July 2019 they start to get to grips with your scenario, Dan Bontrager covers 3 different formlae/scenarios that may help you (you'll have to adjust for GMT and working hours) or, if you can get APEX written, the comment from Benjamin Hogan may help.

Let me know if you want to discuss further?

Thanks,

Matt
Cat SheltonCat Shelton
Hey Matt, thank you - I may well look into the Apex code as i suspect there may be a few scenarios where we need to calculate business hours. It's frustrating that there's no simple solution based on the business hours set up within the settings! In the mean time, this isn't a massive issue for the moment so we might have to live with it. 
Cat SheltonCat Shelton
I couldn't see any Ideas around this, however I feel it would make everyones lives easier so I've created an Idea on Idea exchange for this https://trailblazer.salesforce.com/ideaView?id=0874V000000ciMCQAY 
Cat SheltonCat Shelton
https://www.professorflow.com/post/salesforce-business-hours - I was on a call with our consultant and he recommended this, it installs an Apex which can be referenced within a Flow. This works amazingly! 
This was selected as the best answer
Judist CrewsJudist Crews
Hi Cat,

I am trying to implement the solution you referenced. When you say it installs Apex which can be referenced in a flow, can you elaborate on what you mean. Is that a workflow? If so can you provide a short explanation of the criteria and action you use to update a field with elapsed time between two date/times?
Cat SheltonCat Shelton
Hi Judist, 

I'm having issues attaching some screenshots to demonstrate the issue, but you install the Apex code from the link above, and then you can add 'Business Hours' as an Apex action within a Flow. 

Please do feel free to contact me directly and I can email you over some screenshots of our set up. 

Thanks