In a workflow rule, the rule criteria is either 'criteria are met' or 'formula evaluates to true'.
In the former case, the individual expressions you enter are turned into SOQL by SFDC and hence the special date filters are available to you
But, in the second case, and the one you are using, the expression is evaluated like a formula field which does not have special date filters as an option - this is because the expression could be much more complex than something resolvable to a SOQL where clause
To work around this, create a formula field of type Number (1 decimal) on Opportunity.CloseDate_YYYY_Qn like this:
YEAR(CloseDate) +
CASE(MONTH(CloseDate),
1,1,
2,1,
3,1,
4,2,
5,2,
6,2,
7,3,
8,3,
9,3,
10,4,
11,4,
4
)/10)
Create another Number formula field on Opportunity called Today_YYYY_Qn__c as:
YEAR(TODAY()) +
CASE(MONTH(TODAY()),
1,1,
2,1,
3,1,
4,2,
5,2,
6,2,
7,3,
8,3,
9,3,
10,4,
11,4,
4)
Now, your workflow rule can do simple comparison of CloseDate_YYYY_Qn > Today_YYYY_Qn__c
as the resulting numbers (e.g. 2015.1 > 2014.4 will sort properly and hence will compare correctly as time goes forward
These formula fields have utility in other use cases and can inspire extension to do other relative date criteria in 'formula evaluates to true' scenarios.
Note that you can't compare strings in formula fields as in
"2015-Q1" > "2014-Q4"
even though lexicographically they sort properly. The ">" operator isn't available for text operands.
Here is my attempt at your requirement with both a verbose explanation and a minified version:
IF
(
OR
(
//First Scenario Where Sales Type is Open Order and TODAY's date is greater than Scheduled Ship Date
AND
(
TODAY() > Scheduled_Ship_Date__c,
Line_Status__c = 'OPEN'
),
//Second Scenario where Sales Type is Closed Order AND
//Actual Ship Date is greater than Scheduled Ship Date
//OR
//Fulfillment Date is greater than Scheduled Ship Date
AND
(
OR
(
Actual_Shipment_Date__c > Scheduled_Ship_Date__c,
DATEVALUE(Fulfillment_Date__c) > Scheduled_Ship_Date__c,
),
Line_Status__c = 'CLOSED'
)
),
True,
False
)
EDIT Minified:
IF(OR(AND((TODAY() > Scheduled_Ship_Date__c),(Line_Status__c = 'OPEN')),AND(OR((Actual_Shipment_Date__c > Scheduled_Ship_Date__c),(DATEVALUE(Fulfillment_Date__c) > Scheduled_Ship_Date__c)),(Line_Status__c = 'CLOSED'))),True,False)
Best Answer
The start Bracket
(
ofIF
is missing. Try this one:-