4

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

Improve this question
0

3 Answers 3

Reset to default
7

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)
Improve this answer
2
  • I tried this and its working fine for calculating business days. Thanks.
    – Anitha J J
    Sep 9, 2015 at 6:15
  • Thanks Anitha,But I am trying to achieve date as a result. Is it possible by any means?
    – syed88
    Sep 10, 2015 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 ) ) )
Improve this answer
3
  • 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, 2015 at 4:43
  • So it's 16 business days after the Confirmed_Enrolled_Date__c that you're after?
    – Nick Cook
    Sep 9, 2015 at 4:54
  • Yes @Nick , I am looking for a date which will be 16 business days after enrolled confirmed date
    – syed88
    Sep 10, 2015 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

Improve this answer
1
  • it goes to 404.
    – grepit
    Sep 20, 2022 at 18:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .