Useful Salesforce.com Special Date Values and Filter Criteria Examples
One thing that defines great sys admins and report writers in Salesforce.com is their ability to filter on desired data in Views, Reports, Workflow Rules, Assignment Rules, etc. In support of this, in all of its editions Salesforce has given us 33 super useful Special Date values that we can use to create dynamic filter criteria, such as “Tomorrow”, “Last (n number of) Days”, “Next Quarter”, “This Week” , “Next Fiscal Year” and so on. You can see the full list of these useful values under Help at Special Date Values for Filter Criteria.
A few examples of Special Date Values in action:
- In a Lead View, to see a dynamic list of Leads created last month, use:
- On a Report, to see a list of Opportunities with a Close Date exactly 7 days in the future:
- To Assign Cases for Assets with a Purchase Date in the current or last 4 Quarters to a Queue, and ones prior to that to a specific support rep:
A few considerations:
- Special Date Values are not Case Sensitive, and thus can be written as LAST 7 DAYS or last 7 days or any variation in between.
- Special Date Values are effected by each user’s Locale settings within Salesforce, in determining the 12:00am start of the specific time period.
- It is generally best to use the EQUALS or NOT EQUALS operators when using Special Date Values. It is easy to get into trouble when attempting to use other operators such as GREATER THAN or LESS THAN. For example, if you try to filter on something that has a Created Date GREATER THAN LAST 7 DAYS, you will never see any results- as that date would have to be in the future!
- LAST n DAYS includes today, but LAST n QUARTERS does not include the current quarter and LAST n YEARS does not include the current year.
- Last Month includes anything in the Last calendar month. If your your filter needs to be rolling, use LAST n DAYS.
- Salesforce provides some helpful tips for entering filter criteria
Use the comments section to share any examples have you’ve come up with!