Sunday, September 11, 2011

Exclude Saturdays and Sundays while calculating the Due Date

Requirement: Calculate Due Date from Start Date and Number Of Business Days
Due Date = Start Date + Number of Days [excluding Saturdays and Sundays]
An apex trigger to calculate the Due date on an insert or update is rather time consuming than just creating a formula field with some logic.

The below formula field calculates the Due Date [which is the new formula] provided the Start Date [StartDate__c] and Number Of Days to be added to the Start Date [NumberOfDays__c] excluding Saturdays and Sundays.

CASE(
MOD(StartDate__c - DATE(1900, 1, 7), 7),
0, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c-1)/5)*2,
1, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c)/5)*2,
2, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+1)/5)*2,
3, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+2)/5)*2,
4, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+3)/5)*2,
5, (StartDate__c) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2,
6, (StartDate__c) - IF(NumberOfDays__c>0,1,0) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2,
null)

3 comments:

  1. Nice work man....!!!!

    ReplyDelete
  2. Can you modify this formula to calculate NumberOfDays__c based on start date and end date without weekend? Thanks,

    ReplyDelete