3

I have a formula that is calculating the date from another date field + number of days and returning a date.

test census dates field = Confirmed_Enrolled_Date__c + 16

I want to exclude weekends from the return date. IS there any way of achieving this.

Thankyou All

5

Here is a link with similar solution.

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)
  • I tried this and its working fine for calculating business days. Thanks. – Anitha J J Sep 9 '15 at 6:15
  • Thanks Anitha,But I am trying to achieve date as a result. Is it possible by any means? – syed88 Sep 10 '15 at 0:22
2

There are some great date functions available here:

https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm

Specifically you might find the Finding the Number of Business Days Between Two Dates helpful:

(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 ) ) )
  • Thanks for the link Nick.. but what I am trying to achieve is through one date field which I am using to calculate the other date. Do I have to make another date field to calculate the date – syed88 Sep 9 '15 at 4:43
  • So it's 16 business days after the Confirmed_Enrolled_Date__c that you're after? – Nick Cook Sep 9 '15 at 4:54
  • Yes @Nick , I am looking for a date which will be 16 business days after enrolled confirmed date – syed88 Sep 10 '15 at 3:50
1

hi below article from salesforce explain exactly what you are looking for . thanks

https://help.salesforce.com/HTViewSolution?id=000003920&language=en_US

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.