Find Date Values with Formulas

Learning Objectives

After completing this unit, you’ll be able to:

• Find the beginning or end of a period.
  
• Find the month and year of a date.
  
• Determine if a date is between a start and an end date.
  
• Calculate dates a specified number of days or months before or after a date.
  
• Calculate the number of days or months between two dates.
  

Find the Beginning and End of a Time Period

Now that you know how to format dates, you can perform more complex calculations. In this unit, you learn how to find relative dates and the time between dates. We start here by finding the date at the beginning and end of a time period.

Spiff functions can find the beginning and end of years, fiscal years, quarters, fiscal quarters, months, and periods. And these functions work best when you combine them with the other date functions you learned about: date, statement period start and end date, and dates in connector fields, calculated fields, and custom fields.

Beginning and end of a period functions have a similar syntax. As an example, let’s use the beginning of the year function. The syntax for the function is beginning_of_year(). If you enter any date in a year, it returns January 1.

To make the formula dynamic, use a statement date reference, such as beginning_of_year(statement_period.start_date). On any statement period during a year, it’ll return January 1 of the same year. Depending on your formula, you can use the beginning-of-day or end-of-day functions on the period’s start or end date to add timestamps.

The other beginning and end of a period functions are ‌similar. Check out this table—you’ll note a pattern here.

If you want to know more about these date functions, look them up in the function library in Designer.

Use Month and Year Functions

The month and year functions are almost identical. To return the number of the month—such as 1 for January or 6 for June—use month(). To return the four-digit number for the year, use year().

You can use both of these functions with relative statement dates. For example, use month to find a statement period’s start month with month(statement_period.start_date). Similarly, find the year of the statement period start date with year(statement_period.start_date).

Find a Date Between Two Other Dates

The date-between function finds if a date is between a start and end date. ‌It returns true if the date is between the start and end dates, or false if it isn’t. The syntax for the function requires three dates:

1. A date to check
   
2. A start date
   
3. An end date
   

The syntax for the function is date_between?(date to check, start date, end date).

For example, if you want to check if the date a ramp started for a rep was in a statement period, you can use this formula date_between?(date(RampStartDate), statement_period.start_date, statement_period.end_date). If RampStartDate is formatted as a date, you can leave out the date function.

In this example, the RampStartDate was true because it was between the statement period’s start and end dates. If the RampStartDate was outside the start and end dates, the formula would return false.

Return Days Ago and Months Ago

To find a date a certain number of days or months before a date, use the days ago and months ago functions. Each of the functions requires two pieces of information.

1. A start date
   
2. The number of days or months difference from the start date
   

The syntax for these functions is days_ago(start date, number of days) and months_ago(start date, number of months).

For example, to find the date 14 days before the close date of a deal—represented by CloseDate—use the formula days_ago(CloseDate, 14).

After you save your calculation, the result shows in the dataview. Compare it with the CloseDate field to check that it’s returning the correct date, fourteen days before the CloseDate. You can check the calculation in a datasheet or a worksheet.

Days ago and months ago functions don’t only look backward, though. You can find a date after the start date by using a negative number in the number of days or months difference from the start date. For example, say you want to find the date 14 days after the close date of a deal. You use the formula days_ago(CloseDate, -14).

Note that the days ago and days ago negative formulas return different results based on the same date.

Use Days to and Months to Functions

To find the number of days or months between two dates, you use the days to and months to functions. Both functions require a start date and an end date. The syntax is days_to(date 1, date 2) or months_to(date 1, date 2). And you can use either function with the if function.

For example, your company awards a $100 bonus to reps if the go-live date of their deal is within two months of when the deal closed. So you use the formula if(months_to(CloseDate, GoLiveDate) <= 2, 100, 0). The months to function returns how many months have passed between the deal’s close date and the go-live date. The if function checks to see if the result is less than the required time, and if it is, it rewards 100 to the rep for each deal.

Time to Wrap Up This Badge

Relative dates in Spiff help you create dynamic and accurate reports. No more manually adjusting specific dates each time!

In this module, you learned how to format calculations and use statement_period.start_date and statement_period.end_date to update calculations without manually having to change a date. You also learned how to format dates to perform calculations, plus some helpful functions to help you find dates relative to other dates, like the beginning of a quarter, the days before a date, and more.

These tools are essential for creating complex and precise date-based calculations in Spiff. What calculation will you build first?

Resources

• Trailhead: Introduction to Salesforce Spiff Designer
• Trailhead: Salesforce Spiff Sheets