Format Dates for Calculations
Learning Objectives
After completing this unit, you’ll be able to:
- Explain the importance of relative dates for dynamic calculations.
- Use relative date references.
- Use the date function to specify that a value is a date.
- Add timestamps to date values.
Before You Start
Before you start this module, make sure you complete this content. The work you do here builds on the concepts and work you do in that content.
Why Relative Dates Matter
Salesforce Spiff is a leading sales compensation software that automates and simplifies the process of calculating and managing sales commissions. Time is a critical element of any report or statement in Spiff. You calculate commissions for a specific rep, on a specific plan, for a specific period. Often, only the period changes over time in those reports.
That’s where relative dates become useful.
A relative date is a date defined in relation to another date, and isn’t a fixed calendar date. As an example, the word yesterday refers to a relative date for any given day, but the actual calendar date changes each day.
In Spiff, relative dates can be used in dynamic calculations to keep your data statements and reports accurate and save you time.
For example, you can calculate commissions for September by pulling all deals that closed that month. You can create a calculation that looks for all deals with close dates from September 1 through September 30. That’s great for that specific one-time report, but when you want to pull closed deals for October or any subsequent month, you have to change the specific dates.
Instead, you can use relative dates in a dynamic calculation to create your report. Your calculation can look for deals where the close date is greater than or equal to the beginning of the current month, and less than or equal to the last day of the current month. This logic works every month and you don’t have to update or change any of your logic to pull the current month’s deals.
In this module, you learn about the functions that help you create dynamic calculations with relative dates. You learn how to find beginning and end dates, months and years, dates between two dates, and the months and days since or until a date. Let’s get started.
Add Relative Statement Date References
There are two primary relative statement date references you can use in Spiff—the statement period start date and the statement period end date.
The syntax for the period start date is statement_period.start_date. The syntax for the period end date is statement_period.end_date.
Use these references so the system updates calculations and datasets relative to the statement that is being calculated.
Define Date Values
For Spiff to perform calculations with dates, the system must know that a value is a date. Use the date() function to define data as a date.
Sometimes you use the date function when a field value is a date, but the data type of the field isn’t marked as a date. In these situations, Spiff must be told that a field is a date to complete some calculations and use some formulas, such as when finding the beginning of a year or the number of days between two dates.
For example, imagine you have a plan assignment custom field called RampStartDate. In Designer, tell Spiff that the RampStartDate is a date by using a date function such as date(plan_assignment.RampStartDate). Now that Spiff recognizes the field as a date, you can use any of the date functions you learn about later in this module.
A second situation where you use the date function is when you want to use a fixed date in a calculation. Yes, you just learned how cool and useful dynamic calculations are, but sometimes you still want to use a fixed date. In those cases, use the date function to tell Spiff that the text you entered is a date.
For example, imagine you want to create a data filter that only returns deals with close dates after December 31, 2024. In these situations, you can use a formula such as CloseDate > date(“2024-12-31”).
Notice that the date here’s in the yyyy-mm-dd format. Spiff requires that format to recognize the value as the correct date.
Add Timestamps
While all dates are formatted as yyyy-mm-dd, some dates also include a timestamp value. When date fields are compared in Spiff, they must both include or exclude a timestamp. If one has a timestamp but the other doesn’t, the calculation will return an error.
For example, imagine you're working with two date values: January 31 and January 15 at 4 PM. The second date includes a timestamp. If you try to run calculations on these two dates in Salesforce, the calculation will return an error because the dates are in different formats.
To fix this, add a timestamp to the date without one, so both dates have the same format. To do this, use the beginning_of_day() or end_of_day() functions.
So, in the example, you use the beginning-of-day function on the January 31 date to add a timestamp for the start of that calendar day. The syntax for this formula is beginning_of_day(date(“2025-01-31”)).
The function converts January 31 to the same date with a timestamp of midnight.
Now, you can use the end-of-day function on the same date. The syntax for the formula is end_of_day(date(“2025-01-31”)).
The date has a new timestamp for the very end of the day, one second before midnight.
How does this work in a common situation? Say you want to filter all deals created in January, between January 1 and January 31. If you enter the filter formula CreatedAt >= date(“2025-01-01”) AND CreatedAt <= date(“2025-01-31”) it’ll return an error.
Why? The data in the CreatedAt field includes a timestamp, but the filter values don’t. All dates in a formula must be in the same format.
You can solve this by adding beginning-of-day and end-of-day functions to your filter formula. The new formula is CreatedAt >= beginning_of_day(date(“2025-01-01”)) AND CreatedAt <= end_of_day(date(“2025-01-31”)).
You can take this a step further using the dynamic statement period start and end dates. The syntax for that formula is CreatedAt >= beginning_of_day(statement_period.start_date) AND CreatedAt <= end_of_day(statement_period.end_date). The beginning and end of day functions also convert the statement period values to include time stamps.
Now that you understand how to use relative dates and format dates for calculations and formulas, let’s learn more functions to calculate dates. In the next unit, you learn how to find the beginning and end of a period, find the month and year from a date, determine if a date is in a period, and more.