Use Formulas to Solve Problems in

Use Formulas to Solve Problems with SalesForce.comFormulas are incredibly useful in overcoming the challenges you face as an Admin, Report Writer, Customizer or Developer of a Salesforce system. Learning how to use them can seem daunting; however, through practice and by leveraging the work of others, you can progress quickly. Below is a quick overview overview of formulas, suggestions for useful resources, and a recent example from the trenches of report writing.

In its Help documentation, Salesforce defines:

A formula is an algorithm that derives its value from other fields, expressions, or values. Formulas can help you automatically calculate the value of a field based on other fields.

Formulas can be used in many places in the system, such as Reports, Custom Fields, Workflow Rules, Approval Processes, Validation Rules, Custom Buttons/Links and so on. Since ’08 we’ve been able to write Cross Object Formulas and can now leverage Lookups to reach across up to 10 related objects to pull in a data value to use in a formula. Similar to in Excel, our ability to understand and use “functions” and “operators” is central to advancing as a formula writer.

My advice on getting started with formulas is to look at the Examples of Advanced Formula fields within Salesforce Help.  Also, there is considerable help built into the formula editor in Salesforce, such as examples of the use of functions and operators, and a built in Check Syntax feature. It is critical to test all conditions of your formula on real data- we often use Views or Reports to see formulas in action across many records.

Here is an example of a recent formula we used to solve a specific problem:

Goal: Create a report showing data by month for a rolling Current and Previous 2 Quarters.

Challenge: Report filters don’t give us an easy way to dynamically pull in records by quarter beyond “Current and Previous FQ”. Using a report filter such as LAST 270 Days cuts into the middle of the month and isn’t precise- we need something that flexes depending on whether we are in the first, second or third month of the current quarter (meaning that the formula needs to include the current and past 6, 7 or 8 months respectively). Our date should always compare greater than equal to the first day of the month those many months ago.

Solution: Create a hidden custom formula field on the object which compares Today’s Date and the value in a Date field on the record, such as the Create Date of the records. The following formula returns “True” is the Opp was created in the Current or Prior 2 Quarters, and False if Created Date is prior to that. We can now create a report which filters on a TRUE value in this formula to determine which records fall into our rolling Current and Previous 2 Quarters. Notes: a) This formula assumes use of calendar quarters. b) The else_result in this formula will always return FALSE, but, given system constraints on the CreatedDate field and our predictable 12 months in a year, should never arise.


   DATEVALUE(CreatedDate) >=
      1, DATE(YEAR(TODAY())-1,7,1),
      2, DATE(YEAR(TODAY())-1,7,1),
      3, DATE(YEAR(TODAY())-1,7,1),
      4, DATE(YEAR(TODAY())-1,10,1),
      5, DATE(YEAR(TODAY())-1,10,1),
      6, DATE(YEAR(TODAY())-1,10,1),
      7, DATE(YEAR(TODAY()),1,1),
      8, DATE(YEAR(TODAY()),1,1),
      9, DATE(YEAR(TODAY()),1,1),
      10, DATE(YEAR(TODAY()),4,1),
      11, DATE(YEAR(TODAY()),4,1),
      12, DATE(YEAR(TODAY()),4,1),

Three other formula related posts on our site you might be interested in include:

Photo credit: wburris

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!