Skip to main content
Topics#Data ManagementQuestion de BLD Admin
BLD Admin a posé une question dans #Data Management
14 mars 2018, 15:01

Use standard date field to calculate 30 business days "end" date

I have a standard date field (start date). I need to use the start date to calculate 30 business days (end date). I'm having trouble with the formula working properly to do this. Additionally (side note), users must be able to modify the end date even though it was calculated previously with using the start date. 

 

Here is the formula I am using:

 

IF(MOD(Trial_Start_Date__c- DATE(2000, 1, 6), 7) > 2,

 

Trial_Start_Date__c + 30,

 

IF(MOD(Trial_Start_Date__c - DATE(2000, 1, 6), 7) > 0,

 

Trial_Start_Date__c + 30, Trial_Start_Date__c + 30

 

)

 

)

 

If I populate the start date as 3/14/2018, then end date calculates to 4/13/2018, which is 22 business days. I'm not sure what I'm doing wrong here. Any help would be much appreciated.

 

Thanks, in advance
2 réponses
  1. Aamir Khan (Salesforce)
    14 mars 2018, 15:17
    Aloha,

     

    Calculate the number of days between two dates while excluding weekends or weekdays.

     

    Formulas provided:

     

    1. Weekday Count Formula

     

    2. Weekend Days Count Formula

     

    Steps to create:

     

    1. Create a formula field that returns a number.

     

    2. Paste in one of the two formulas.

     

    3. Replace StartDate__c and EndDate__c with your custom field values.

     

    4. If using Date/Time fields:

     

    Replace with DATEVALUE(YourCustomDateTime__c) instead.

     

    Notes:

     

    Weekdays are defined as Mon-Fri and weekends as Sat & Sun

     

    Holidays are not addressed

     

    June 24, 1985 is a long distant Monday used as a reference point

     

    The result will include BOTH the START and END dates.

     

    Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.

     

    Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.

     

    Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.

     

    If you use another Formula field as the start or end dates you MAY hit a compilation limit.

     

    Workaround - Use workflow rules to save the output of the formula fields into a regular date field.

     

    REMINDER: Be sure to TEST the formulas FIRST.

     

    Weekday Count Formula:
    CASE(MOD( StartDate__c - DATE(1985,6,24),7), 
      0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
      1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
      2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
      3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
      4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
      5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
      6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
      999) 
      + 
      (FLOOR(( EndDate__c - StartDate__c )/7)*5)

     

    Weekend Days Count Formula:
    CASE(MOD( StartDate__c - DATE(1985,6,24),7),
    0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
    1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
    2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
    3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
    4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
    5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
    6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
    999)
    +
    (FLOOR(( EndDate__c - StartDate__c )/7)*2)

     

    Please like if it helps, .

     

    Please mark solved if it works.

     

     

    Mahalo,

     

    Aamir AH Khan,

     

    Salesforce Expert
Rédiger une réponse...
0/9000