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")))))
  • 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? – Guy Feb 4 at 22:31
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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.