0

I have a Task Process Builder based on Opportunity Stages. I am trying to create a formula for the date time due field based on a date field (Internal Deadline). I need the formula for the Date Time Due to be 6pm for 2, 3 and 4 days before the Internal Deadline Date for 3 different tasked not including weekends. I can get the formula to work but I can not figure out how to not include weekends. Please advise what I am doing wrong. Thank you.

IF(MOD(TODAY() - DATE(1900,1,15),7)<5, (DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")-3),
(CASE(MOD(TODAY()- DATE(1900,1,15),7),
1,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 3),
2,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 4),
3,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 5),
4,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 3),
5,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 3),
6,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 3),
7,(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")- 3),
(DATETIMEVALUE(TEXT(YEAR([Opportunity].Internal_Deadline__c ))&"-"& TEXT(MONTH([Opportunity].Internal_Deadline__c ))&"-"& TEXT(DAY([Opportunity].Internal_Deadline__c )) &" "& "23:00:00")))))
1
  • Hi @DoreIN, welcome to Salesforce StackExchange. A question that will help us help you: do you understand what the formula is doing? I.e. do you know what MOD(TODAY() - DATE(1900,1,15),7) does? Feb 4, 2019 at 22:31

1 Answer 1

0

Here is the formula that worked

IF(ISBLANK(C_S_Date__c ),CASE(MOD( Date_RFP_Received__c - DATE(1985,6,24),7), 

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

CASE(MOD( Date_RFP_Received__c - DATE(1985,6,24),7), 

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

You must log in to answer this question.

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