Skip to main content
Salesforce

Return to Question

Put Code inside the Code Block
Source Link
edit approved Feb 7, 2018 at 9:47
Nachiket Deshpande
edit approved Feb 7, 2018 at 9:47
Nachiket Deshpande
  • 2.3k
  • 7
  • 28
  • 57

IF( AND(ISBLANK(Sales_accepted_date__c),ISBLANK(Sales_Rejected_Date__c)) ,

CASE(IF( NOT( ISBLANK(SALES_accepted_date__c)) , CASE(MOD( createddate - DATE(1985,6,24),7), 0 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( TODAY() - createddate )/7)*5),

IF( NOT( ISBLANK(SALES_Rejected_Date__c)) , CASE(MOD( createddate - DATE(1985,6,24),7), 0 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( SALES_Rejected_Date__c - createddate )/7)*5)) )))

IF(
 AND(ISBLANK(Sales_accepted_date__c),ISBLANK(Sales_Rejected_Date__c)) ,

 CASE(IF( NOT( ISBLANK(SALES_accepted_date__c)) ,
CASE(MOD( createddate - DATE(1985,6,24),7), 
  0 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( TODAY() - createddate )/7)*5),

IF( NOT( ISBLANK(SALES_Rejected_Date__c)) ,
CASE(MOD( createddate - DATE(1985,6,24),7), 
  0 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( SALES_Rejected_Date__c - createddate )/7)*5))
)))

IF( AND(ISBLANK(Sales_accepted_date__c),ISBLANK(Sales_Rejected_Date__c)) ,

CASE(IF( NOT( ISBLANK(SALES_accepted_date__c)) , CASE(MOD( createddate - DATE(1985,6,24),7), 0 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( TODAY() - createddate )/7)*5),

IF( NOT( ISBLANK(SALES_Rejected_Date__c)) , CASE(MOD( createddate - DATE(1985,6,24),7), 0 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( SALES_Rejected_Date__c - createddate )/7)*5)) )))

IF(
 AND(ISBLANK(Sales_accepted_date__c),ISBLANK(Sales_Rejected_Date__c)) ,

 CASE(IF( NOT( ISBLANK(SALES_accepted_date__c)) ,
CASE(MOD( createddate - DATE(1985,6,24),7), 
  0 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( TODAY() - createddate )/7)*5),

IF( NOT( ISBLANK(SALES_Rejected_Date__c)) ,
CASE(MOD( createddate - DATE(1985,6,24),7), 
  0 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( SALES_Rejected_Date__c - createddate )/7)*5))
)))
Source Link
Shiva.venu
Shiva.venu
  • 5
  • 4

Custom Formula Error

I have a requirement where I need to calculate the date difference(no of days) between the leads accepted or rejected by sales on a custom field. The date calculated should only consider the working days which excludes the weekends. I try creating this formula but it throws some parameter error also not sure if this will work as expected. Please help me to correct this formula also suggest if there are any better ways to achieve this

IF( AND(ISBLANK(Sales_accepted_date__c),ISBLANK(Sales_Rejected_Date__c)) ,

CASE(IF( NOT( ISBLANK(SALES_accepted_date__c)) , CASE(MOD( createddate - DATE(1985,6,24),7), 0 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( SALES_accepted_date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( TODAY() - createddate )/7)*5),

IF( NOT( ISBLANK(SALES_Rejected_Date__c)) , CASE(MOD( createddate - DATE(1985,6,24),7), 0 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( SALES_Rejected_Date__c - createddate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( SALES_Rejected_Date__c - createddate )/7)*5)) )))