function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Arine YonArine Yon 

required a formula field

I am trying to create a formula field which should calculate the days and set to next date but it should not add weekends days i-e Saturday and Sunday . I Have one date type field and a number field for adding days and calculated date show the date. Need help Thanks in advance. example Start Date : 08/07/2017 Add days : 2 calculated date : 12/07/2017 (required)
Best Answer chosen by Arine Yon
Akshay_DhimanAkshay_Dhiman

Hi Arine,
Use this Formula

CASE(
MOD(start_date__c - DATE(1900, 1, 7), 7),
(start_date__c) +  Add_Days__c  + FLOOR((Add_Days__c-1)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c+1)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c+2)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c+Add_Days__c)/5)*2,
(start_date__c) + Add_Days__c + CEILING((Add_Days__c)/5)*2,
(start_date__c) - IF(Add_Days__c>0,1,0) + Add_Days__c + CEILING((Add_Days__c)/5)*2,
null)

In the above formula field “start_date__c”  is the date which will be given by the user
And  “Add_Days__c”  is the number type field which is use to add number of days.
The resulted date will show the date except counting of weekend days.
You can add numbers of days as you want without limitations.

If this answers your query please mark this question as a solved so that it can be filtered out from unsolved questions.

Regards
Akshay

All Answers

Raj VakatiRaj Vakati
Hi,

Use this Formal. Replace number 2 with the field that used to store the number of days. I am just directly passing 2  days in this formula. Refer this link 
http://salesforce-shruthi.blogspot.com/2011/09/exclude-saturdays-and-sundays-while.html 
Data Type	Formula	 	 
CASE( 
MOD( ttttteee__SLAExpirationDate__c - DATE(1900, 1, 7), 7), 
0, (ttttteee__SLAExpirationDate__c ) + 2+ FLOOR((2-1)/5)*2, 
1, (ttttteee__SLAExpirationDate__c ) + 2+ FLOOR((2)/5)*2, 
2, (ttttteee__SLAExpirationDate__c ) + 2 + FLOOR((2+1)/5)*2, 
3, (ttttteee__SLAExpirationDate__c ) + 2+ FLOOR((2+2)/5)*2, 
4, (ttttteee__SLAExpirationDate__c ) + 2+ FLOOR((2+3)/5)*2, 
5, (ttttteee__SLAExpirationDate__c ) + 2 + CEILING((2)/5)*2, 
6, (ttttteee__SLAExpirationDate__c ) - IF(2>0,1,0) + 2+ CEILING((2)/5)*2, 
null)

 

 
Akshay_DhimanAkshay_Dhiman

Hi Arine,
Use this Formula

CASE(
MOD(start_date__c - DATE(1900, 1, 7), 7),
(start_date__c) +  Add_Days__c  + FLOOR((Add_Days__c-1)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c+1)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c+2)/5)*2,
(start_date__c) + Add_Days__c + FLOOR((Add_Days__c+Add_Days__c)/5)*2,
(start_date__c) + Add_Days__c + CEILING((Add_Days__c)/5)*2,
(start_date__c) - IF(Add_Days__c>0,1,0) + Add_Days__c + CEILING((Add_Days__c)/5)*2,
null)

In the above formula field “start_date__c”  is the date which will be given by the user
And  “Add_Days__c”  is the number type field which is use to add number of days.
The resulted date will show the date except counting of weekend days.
You can add numbers of days as you want without limitations.

If this answers your query please mark this question as a solved so that it can be filtered out from unsolved questions.

Regards
Akshay
This was selected as the best answer
Arine YonArine Yon
Hey! Akshay Thanks for answer and explanation .
Akshay_DhimanAkshay_Dhiman

Hi Arine,
It's my pleasure to help you and Thanks for selecting my answer as a best :)
Regards,
Akshay