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
SFDC New learnerSFDC New learner 

getting problem with formula


Hi All,
I am trying to get the business days excluding the weekends and including startdate and enddate.
 Below formula I am using to get the business days.
CASE( 
MOD(TODAY()- DATE(1900, 1, 7), 7), 
0, (TODAY()) + BusinessDays__c+ FLOOR((BusinessDays__c-1)/5)*2, 
1, (TODAY()) + BusinessDays__c+ FLOOR((BusinessDays__c)/5)*2, 
2, (TODAY()) + BusinessDays__c+ FLOOR((BusinessDays__c+1)/5)*2, 
3, (TODAY()) + BusinessDays__c+ FLOOR((BusinessDays__c+2)/5)*2, 
4, (TODAY()) + BusinessDays__c+ FLOOR((BusinessDays__c+3)/5)*2, 
5, (TODAY()) + BusinessDays__c+ CEILING((BusinessDays__c)/5)*2, 
6, (TODAY()) - IF(BusinessDays__c > 0,1,0) + BusinessDays__c + CEILING((BusinessDays__c)/5)*2, 
NULL)

I am expecting say for example if  I give input date as 09/28/2018 , Output I should get 10/12/2018 and no. of Business Days as 11.
If I use above formula ,It is counting from the next day instead of from the same Start day.

And also is there a way can I show the Business days label instead of giving number as 11.

I tried to create a formula and also written Apex class .

Any help would be greatly  appreciated.

Thanks,
Sirisha

  
SabrentSabrent
Create a forumula field called 'Business Days' with return type integer then paste the following forumula in the formula editor.
replace, Start_Date__c  and End_Date__c with whatever your field names are.
  
CASE(MOD( Start_Date__c - DATE(1985,6,24),7), 

0 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( End_Date__c- Start_Date__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( End_Date__c- Start_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( End_Date__c - Start_Date__c)/7)*5)

 
Mahboob AljiwalaMahboob Aljiwala
How can i exclude holidays as well ?