I have a formula that has been working fine to calculate a due date based on the value in a field.
The formula looks like this:
CASE( MOD( Date_Submitted_to_Design__c - DATE(1900, 1, 7), 7), 0, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c-1)/5)*2, 1, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c)/5)*2, 2, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c+1)/5)*2, 3, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c+2)/5)*2, 4, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c+3)/5)*2, 5, (Date_Submitted_to_Design__c) + Number_of_Days__c + CEILING((Number_of_Days__c)/5)*2, 6, (Date_Submitted_to_Design__c) - IF(Number_of_Days__c>0,1,0) + Number_of_Days__c + CEILING((Number_of_Days__c)/5)*2, null))
I have discovered that due to a change in processing, I don't want this field to calculate unless the order type is specific because if this field calculates every time, it will mess with another formula we need to put into place.
So, I need to say that if the Contract Type = MDU, then do this calculation. I am not great with formulas, but I thought that would mean I need an AND statement and an ISPICKVAL statement, so I did this:
AND( ISPICKVAL( Order_Type__c = "MDU"),
CASE( MOD( Date_Submitted_to_Design__c - DATE(1900, 1, 7), 7), 0, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c-1)/5)*2, 1, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c)/5)*2, 2, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c+1)/5)*2, 3, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c+2)/5)*2, 4, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c+3)/5)*2, 5, (Date_Submitted_to_Design__c) + Number_of_Days__c + CEILING((Number_of_Days__c)/5)*2, 6, (Date_Submitted_to_Design__c) - IF(Number_of_Days__c>0,1,0) + Number_of_Days__c + CEILING((Number_of_Days__c)/5)*2, null))
This throws an error and says "Error: Field Order_Type__c is a picklist field. Picklist fields are only supported in certain functions"
What am I doing wrong??
Are you trying to do something like this?
IF( TEXT( Order_Type__c ) = 'MDU',
CASE( WEEKDAY( Date_Submitted_to_Design__c ) ,
1, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c-1) / 5) * 2,
2, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c) / 5) * 2,
3, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c + 1) / 5) * 2,
4, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c + 2) / 5) * 2,
5, (Date_Submitted_to_Design__c) + Number_of_Days__c + FLOOR((Number_of_Days__c + 3) / 5) * 2,
6, (Date_Submitted_to_Design__c) + Number_of_Days__c + CEILING((Number_of_Days__c) / 5) * 2,
7, (Date_Submitted_to_Design__c) - IF(Number_of_Days__c > 0,1,0) + Number_of_Days__c + CEILING((Number_of_Days__c) / 5) * 2,
NULL),
NULL)
