Showing posts with label start date. Show all posts
Showing posts with label start date. Show all posts

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)