Date Condition: "Less than or Greater than XX Days"

QUESTION:

How do I create a date condition that says: "Any record older than 45 days"?  I see ways to say "less than or equal to" and then can manually pick a date on the calendar, but this will not work for automated scenarios.

ANSWER:

DemandTools has some special options for creating date conditions, e.g. TODAY, THIS_WEEK, LAST_MONTH, etc., but these all use "equals" as the default operator.

DemandTools DOES NOT currently have a date option that allows the ability to specify options such as "greater than the last XX days." The Salesforce API does not have the same date search capabilities that the Salesforce User Interface does (similar to those available for Salesforce Reports), so this is not a feature we can implement at this time.

However, there is a workaround that can be implemented using a custom formula field for Age.

NOTE:  This formula will ONLY work with Date/Time Fields.  See option #2 for Date fields.

Option #1 for Date/Time Fields (i.e. Created Date)

1. Create a custom formula(number) field on the table that calculates the "Age" based on the date/time field:
   • Round( NOW()- Date/time field ,0)
2. Use this new field with the >,<,<= etc., operators in the condition, e.g. Age >= 45.

Option #2 for Date fields (i.e. Close Date on Opps, Activity Date on Tasks)

1. Create a custom Date field on the table called "Age_Date"
2. Use MassImpact to copy the existing Date into this custom field
3. Create a second custom formula(number) field on the table that calculates the "Age" based on the custom "Age_Date__c" date/time field:
   • ROUND(NOW()- Age_Date__c,0)
4. Use this new field with the >,<,<= etc. operators in the condition, e.g. Age >= 45.

NOTE:  If wanting to create a condition based on "Today" (but not equal today, i.e. > "Today"), use the workaround above and then create the condition where "Age" > 0.