Skip to main content Stream TDX Bengaluru on Salesforce+. Start learning the critical skills you need to build and deploy trusted autonomous agents with Agentforce. Register for free.
error

We made a wrong turn. Try again.

Topics#Data ManagementAlex Christensen's Question
Alex Christensen (CellGate) asked in #Data Management
May 26, 2021, 7:48 PM

calculate number of business days passed since opportunity created date

Looking for a formula to calculate the number of business days passed since the opportunity created date(Date/Time Field)?
3 answers
  1. Tony Trinh (Navi Consulting)
    May 26, 2021, 7:56 PM
    Hi Alex,

     

    You can use this helpful resource:

     

    https://help.salesforce.com/articleView?id=sf.formula_examples_dates.htm&type=5 (https://help.salesforce.com/articleView?id=sf.formula_examples_dates.htm&type=5)

     

    There are a number of date formulas you can use. You would want to use the "Find the Number of Business Days Between Two Dates"

    (5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
    -
    (5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

    where date_1 is the more recent date and date_2 is the older date. Since you are working with Date/Time fields, you would just use a DATEVALUE function to convert from Date/Time to Date like this:

    (5 * ( FLOOR( ( DATEVALUE(date_time_1) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(date_time_1) - DATE( 1900, 1, 8), 7 ) ) )
    -
    (5 * ( FLOOR( ( DATEVALUE(date_time_2) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(date_time_2) - DATE( 1900, 1, 8), 7 ) ) )
  2. Alex Christensen (CellGate)
    May 26, 2021, 8:03 PM
    This formula worked.

     

    (5 * ( FLOOR( TODAY() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(TODAY() - DATE( 1900, 1, 8), 7 ) ) 

     

    -

     

    (5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) )
Write an answer...
0/9000