1

I have a formula field that calculated the difference in business hours between two dates from field MQL_Response__c and field MQL_Created__c. The hours are calculating correctly, but it rounds to a whole number and I would like it to return a decimal to the hundredths place. Here is the formula I'm using:

ROUND( 8 * ( ( 5 * FLOOR( ( DATEVALUE( MQL_Response__c ) - DATE( 1900, 1, 8) ) / 7) + MIN(5, MOD( DATEVALUE( MQL_Response__c ) - DATE( 1900, 1, 8), 7) + MIN( 1, 24 / 8 * ( MOD( MQL_Response__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) ) ) )

( 5 * FLOOR( ( DATEVALUE( MQL_Created__c ) - DATE( 1900, 1, 8) ) / 7) + MIN( 5, MOD( DATEVALUE( MQL_Created__c ) - DATE( 1996, 1, 1), 7 ) + MIN( 1, 24 / 8 * ( MOD( MQL_Created__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) ) ) ) ), 0 )

1

Assuming you got this from: https://help.salesforce.com/s/articleView?id=sf.formula_examples_dates.htm&type=5

Find the Number of Business Hours Between Two Date/Times The formula to find business hours between two Date/Time values expands on the formula to find elapsed business days. It works on the same principle of using a reference Date/Time, in this case 1/8/1900 at 16:00 GMT, or 9:00 AM PDT, and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9:00 AM to 5:00 PM work day.

ROUND( 8 * (
   ( 5 * FLOOR( ( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )

If you look up on the Salesforce Round formula: https://help.salesforce.com/s/articleView?id=sf.bi_integrate_data_prep_recipe_formula_field_numericFunctions_round.htm&language=en_US&r=https%3A%2F%2Fwww.google.com%2F&type=5

You will find that Round(<any number>, <number of digits>). So you want to change the final 0 to a 2 for two decimal places. Fun fact, if you put a negative number there it will round to tens (-1), or hundreds (-2), etc.

I really would like to end it here, but I think the rounding may be redundant.... but Salesforce says... lol. If you find that it still gives you hours, we'll have to add a component for intraday. I can either help if you comment, or you can look at the example above the one you got business hours from and it will show you how to add the minute component.

Finally, I would like to suggest that maybe hours is just fine and dandy... there are lots of assumptions built into this calculation that going to a decimal place would cause a "false sense of accuracy." What do I mean? It is really calculating the number of weekdays, not business days. Your company certainly has holidays... they are not accounted for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.