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:

Date Filter Lead View Example

  • On a Report, to see a list of Opportunities with a Close Date exactly 7 days in the future:

Date Filter Opportunity Report Example

  •  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:

Date Filter Case Assignment Rule Example

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!

 

 

fast-track your mission critical project

start here

March 6, 2012

David Carnes - Founder & CEO

about the author

David Carnes

David’s role as Chairman & Chief Digital Evangelist is centered around driving meaningful client engagement and business development. The key to this is serving as an advisor to OpFocus’ SaaS clients as they scale their revenue operations and embrace digital transformation.

In his early career, David worked in IT and operations for software companies, developing an interest in CRM, marketing automation, and analytics while building out systems, processes, data, and reporting for the business teams he supported. He earned a Masters in Software Engineering and credits a Harvard summer class in database management for opening his eyes to what systems could do to support operations. In founding OpFocus in 2006 David took another step toward focusing on business operations, seizing on Salesforce’s vision and never looking back.

David is a frequent speaker at Salesforce and Salesforce community events around the world, mentors through the Trailblazer Mentorship Program hosts Dashboard Dōjō, and serves as a Platform Champion and a Pi-TaP board member. Due to his involvement in the trailblazer community, David’s recently been awarded the position of Salesforce MVP!