Formula for Business Hours or Diff. between two DateTime Fields #inSalesforce

Formula for Business Hours or Diff. between two DateTime Fields #inSalesforce

Suppose we have date and datetime fields : Date1, Date 2, DateTime1 and DateTime2

Find the number of days between Dates

Simple calculation between two date fields: (return number)

Date1 - Date2 

Find the difference between two date-time fields : (return text)

IF(

  DateTime1 - DateTime2 > 0 ,

  TEXT( FLOOR( DateTime1 - DateTime2 ) ) & " days "

  & TEXT( FLOOR( MOD( (DateTime1 - DateTime2 ) * 24, 24 ) ) ) & " hours "

  & TEXT( ROUND( MOD( (DateTime1 - DateTime2 ) * 24 * 60, 60 ), 0 ) ) & " minutes",

  ""

)

Find business hours between two date-time fields  : (return number)

ROUND( 

8 * (

   ( 5 * FLOOR( ( DATEVALUE( DateTime1 ) - DATE( 1900, 1, 8) ) / 7) +

    MIN(5, 

     MOD( DATEVALUE( DateTime1 ) - DATE( 1900, 1, 8), 7) +

     MIN( 1, 24 / 8 * ( MOD( DateTime1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )

    ) 

   )

   -

   ( 5 * FLOOR( ( DATEVALUE( DateTime2 ) - DATE( 1900, 1, 8) ) / 7) +

     MIN( 5,

      MOD( DATEVALUE( DateTime2 ) - DATE( 1996, 1, 1), 7 ) +

      MIN( 1, 24 / 8 * ( MOD( DateTime2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )

    )

   ) 

  ), 

0 

)