When working in Salesforce, it’s common to compare date fields like CloseDate to the quarter of "today". However, determining if a date is in an earlier or later quarter than the current one requires more than just a comparison of months. It involves combining both the year and the quarter into a single value to make accurate comparisons.
This article will explore:
- Common pitfalls when comparing date fields across quarters.
- The best solution using concatenated
YEARandMONTHvalues to compare quarters. - Use cases for why this is useful.
- Complete example formulas to ensure accuracy.
Why Compare Across Quarters?
Salesforce admins and developers often need to group, filter, or validate data based on whether a date:
- Falls in a past quarter.
- Is in the same quarter as today.
- Belongs to a future quarter.
Use cases include:
- Pipeline Management: Highlighting overdue opportunities from previous quarters or identifying deals expected in future quarters.
- Forecasting: Comparing historical trends against current or future performance.
- Automation: Triggering actions for records based on their placement in a previous or upcoming quarter.
Common Pitfalls in Quarter Comparisons
1. Ignoring Year in Quarter Comparisons
Bad Example:
FLOOR((MONTH(CloseDate) - 1) / 3) + 1 < FLOOR((MONTH(TODAY()) - 1) / 3) + 1
Why It Fails:
- This formula only compares the quarter numbers without considering the year. For example:
- Q1 2023 and Q1 2024 are treated as the same quarter.
- As a result, comparisons become inaccurate when crossing calendar years.
2. Hardcoding Quarter Ranges
Bad Example:
OR(
AND(MONTH(CloseDate) >= 1, MONTH(CloseDate) <= 3, YEAR(CloseDate) < YEAR(TODAY())),
AND(MONTH(CloseDate) >= 4, MONTH(CloseDate) <= 6, YEAR(CloseDate) = YEAR(TODAY()))
)
Why It Fails:
- The formula grows unnecessarily complex as you try to accommodate fiscal years or different year overlaps.
- Hardcoding ranges makes it brittle and error-prone for dynamic processes.
The Best Solution: Concatenating Year and Quarter
To accurately compare quarters, combine the year and quarter into a single comparable value. For example:
- Q1 2024 becomes
20241 - Q2 2023 becomes
20232
This allows you to use basic comparison operators (<, >, =) to determine whether a date is in an earlier or later quarter.
Step 1: Map Months to Quarters
Use a CASE function to map months to their respective quarters. Only use numeric values for the returned value in the CASE function so it can be compared using numeric operators, like '<' and '>'.
CASE(
MONTH(DateField__c),
1, 1,
2, 1,
3, 1,
4, 2,
5, 2,
6, 2,
7, 3,
8, 3,
9, 3,
10, 4,
11, 4,
12, 4,
NULL
)
Step 2: Concatenate Year and Quarter
Combine the year and quarter values into a single numeric value. Put the year attribute first, followed by the month attribute. This is necessary for the comparison operators to work, e.g. '112024' (Nov 2024) will register as less than '122023' (Dec 2023), which is clearly wrong. Writing this with the year first, note that '202411' will correctly register as less than '202412'.
TEXT(YEAR(DateField__c)) &
TEXT(
CASE(
MONTH(DateField__c),
1, 1,
2, 1,
3, 1,
4, 2,
5, 2,
6, 2,
7, 3,
8, 3,
9, 3,
10, 4,
11, 4,
12, 4,
NULL
)
)
Step 3: Create a Formula for Comparison
Use a formula to return values indicating if the date field is in an earlier quarter, same quarter, or later quarter than the current quarter. To do this, compare the concatenated year-month values against each other. You can also refer to a separate formula field indicating that a date field is in current quarter to avoid writing redundant formulas. See here for an article on this subject.
The below formula indicates if DateField__c is in current quarter, an earlier quarter, or a later quarter.
/* Handle for Current Quarter first */
IF(
DateIsInCurrentQuarter__c = 'TRUE',
'Current Quarter',
/* Then, check if DateField__c is in an Earlier Quarter */
IF(
TEXT(YEAR(DateField__c)) &
TEXT(
CASE(
MONTH(DateField__c),
1, 1,
2, 1,
3, 1,
4, 2,
5, 2,
6, 2,
7, 3,
8, 3,
9, 3,
10, 4,
11, 4,
12, 4,
NULL
)
) <
TEXT(YEAR(TODAY())) &
TEXT(
CASE(
MONTH(TODAY()),
1, 1,
2, 1,
3, 1,
4, 2,
5, 2,
6, 2,
7, 3,
8, 3,
9, 3,
10, 4,
11, 4,
12, 4,
NULL
)
),
'Earlier Quarter',
/* Else - DateField__c is in a Later Quarter */
'Later Quarter'
)
)
Use Cases
1. Validation Rules
Example: Prevent users from updating a CloseDate to a quarter in the past. They can only change CloseDate to a
/* Check if the CloseDate's quarter reference is 'Earlier Quarter' */
CloseDateQuarterReference__c = 'Earlier Quarter' &&
ISCHANGED(CloseDate) &&
/* Only validate if the Quarter of the CloseDate was changed, e.g. changing Months within the same quarter is OK and does not trigger the error*/
(
NOT(
TEXT(YEAR(CloseDate)) &
TEXT(
CASE(
MONTH(CloseDate),
1, 1,
2, 1,
3, 1,
4, 2,
5, 2,
6, 2,
7, 3,
8, 3,
9, 3,
10, 4,
11, 4,
12, 4,
NULL
)
)
) =
TEXT(YEAR(PRIORVALUE(CloseDate))) &
TEXT(
CASE(
MONTH(PRIORVALUE(CloseDate)),
1, 1,
2, 1,
3, 1,
4, 2,
5, 2,
6, 2,
7, 3,
8, 3,
9, 3,
10, 4,
11, 4,
12, 4,
NULL
)
)
)
2. Reports and Dashboards
- Filter on Opportunities that are still open, but have a CloseDate in an earlier quarter.
- Group a list of Opportunities by their status of earlier/current/later quarters to view aggregate data across these cohorts.
3. Automation
Trigger processes based on quarter comparisons:
- Notify managers when their team has an open deal with a CloseDate in an earlier quarter.
Conclusion
Using concatenated YEAR and MONTH values to compare quarters ensures robust, accurate, and maintainable logic. While simpler methods might suffice for basic cases, they often fail when crossing years or incorporating fiscal calendars. By leveraging this approach, Salesforce admins and developers can create formulas that are scalable, intuitive, and reliable. Now’s the time to upgrade your quarter comparisons for smarter automation and reporting!
Comments