Skip to main content
Topics#Data ManagementTheresa Petty's Question
Theresa Petty asked in #Data Management
Dec 28, 2012, 7:24 AM

Formula Help

I have created a custom object called 'Project Activities' which has a 'Start Date' field and an 'Activity Duration (days)' field which are manually entered.  I would like to auto-calculate the 'Due Date' field using formula however not sure how to discount Saturdays & Sundays from the calculation.  Ie If the start day = Monday 7th Jan and the duration is 7 working days I would like the formula to calculate the due date as Wed 16th Jan & not Monday 14th Jan.  Can anyone help with this.  I have created a formula 'Start Day' field to calculate the day of the week the activity is due to start and also created a 'No of Weeks' field using formula 'Activity Duration / 5' to reflect working weeks and not calendar weeks but not sure what to do next.
7 answers
  1. Subramanyam Meare (ADP)
    Dec 28, 2012, 9:02 AM
    Try something like this:-

     

    CASE(

     

    MOD(  ( Start_date__c  + Activity _Duration__C ) - DATE(1900, 1, 7),7),

     

    0, ((Start_date__c  + Activity _Duration__C)+1) ,

     

    1, Start_date__c  + Activity _Duration__C,

     

    2, Start_date__c  + Activity _Duration__C,

     

    3,Start_date__c  + Activity _Duration__C,

     

    4, Start_date__c  + Activity _Duration__C,

     

    5, Start_date__c  + Activity _Duration__C,

     

    6, ((Start_date__c  + Activity _Duration__C)+ 2),Null)

     

    Note;- Replace the Field name mentioned here with Your field names by clikcing on "INSERT FIELDS" button under formula.
Write an answer...
0/9000