2

I have a formula field that calculates business hours (M- F > 09:00 AM to 5:00 PM- 8 hours) between two date time fields. i actually used exactly same formula that sf helps provided.

https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

But it still gives wrong result. My User is on PDT (GMT -8 ) Any advise ?

MY FORMULA :

ROUND( 8 * (
   ( 5 * FLOOR( ( DATEVALUE(  Closed_Date__c  ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( Closed_Date__c ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( Closed_Date__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE(  Auth_Received_Date__c  ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( Auth_Received_Date__c ) - DATE( 1900, 1, 8), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( Auth_Received_Date__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
    )
   ) 
  ), 
2 )
/* 14:00 (EST) = 09:00 (GMT)*/
/* 1900,1,8 a random monday*/

AND OUTPUT :

enter image description here

1st row suppose to be 12.45 (hours) 2nd row suppose to be 26.00 (hours) 3rd row suppose to be 11.45 (hours)

  • See this note in your code /* 14:00 (EST) = 09:00 (GMT)*/ and make the appropriate adjustments. PST is 4 hours earlier than EST. Not going to mention the effects of DST which are well known. – crmprogdev May 16 '16 at 13:47
  • Made it 17:00 in formula , but did not make difference.Thanks anyway – erkan çipil May 16 '16 at 13:52
0

9AM GMT is 2AM PST. It gets earlier as you head west. Your formula should look like below:

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 2: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 2:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )
  • But SF documents convert 9:00 AM PST to 16:00 GMT. Do you think , they are wrong ? 1/8/1900 at 16:00 GMT (9 a.m. PDT) – erkan çipil May 16 '16 at 14:31
  • I know they are. Go to the World Clock Time Zone Converter and see for yourself. That said, I ran the calculation based on 9AM GMT to find what the same time would be in EST. If you want 9AM EST converted to GMT, the number to use would be 2PM or 14:00. – crmprogdev May 16 '16 at 17:32

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.