All posts
Salesforce

Check if a Salesforce date field is in the current quarter

Sometimes, you need to check if a date field is in a later quarter or earlier quarter than the current quarter. To do this, see this article instead.

Salesforce

Note: This article describes how to check whether a date field is in the same quarter as the current quarter.

Sometimes, you need to check if a date field is in a later quarter or earlier quarter than the current quarter. To do this, see this article instead.

Determining whether a date field (like CloseDate) falls in the current quarter seems straightforward, but it can be deceptively tricky. Many admins attempt to solve this with formulas, but common approaches often suffer from inaccuracies, maintenance challenges, or unnecessary complexity.

This blog post will explore:

  1. Common approaches for checking if a date falls in the current quarter.
  2. The pitfalls of these methods.
  3. The best solution: a clean, maintainable formula using the CASE() function.

Why Quarter Detection Matters

Salesforce professionals frequently group date values by fiscal quarter for things like:

  • Forecasting and pipeline dashboards
  • Validation logic (e.g. required fields if a deal is set to close this quarter)
  • Time-based automation (e.g. alert if closing soon)

A misaligned formula can cause:

  • Inaccurate reports
  • Broken logic
  • Bad data downstream

Note: any date field will allow out of the box grouping by quarter in Salesforce reports, but only in reports. When a date field is grouped in a report, click the drop down next to the column header and you can choose to group by day, week, month, quarter, or year. The quarter grouping option is driven by how your org’s fiscal calendar is set up in Company Information (learn more).

If you need a quarter check to use for other things (list views, validations, automation) then read on below…

Common Approaches and Their Pitfalls

1. Using Direct Comparisons with MONTH()

Example:

OR(
    AND(MONTH(TODAY()) = 1, MONTH(CloseDate) = 1),
    AND(MONTH(TODAY()) = 2, MONTH(CloseDate) IN (1, 2)),
    AND(MONTH(TODAY()) = 3, MONTH(CloseDate) IN (1, 2, 3)),
    ...
)

Why It’s Used:

  • Conceptually simple - this formula compares the numerical month value (e.g. April = 4) of two date fields together and returns True if it’s a match

Pitfalls:

  • Tedious to write, harder to update, and doesn’t account for fiscal quarters easily
  • Only checking the month value means the year is neglected. In other words, April of 1913 would return ‘4’ and so would April of 2045. Should these be considered a match? Probably not.

2. Month-Based Quarter Calculation with FLOOR()

Example:

FLOOR((MONTH(CloseDate) - 1) / 3) + 1 = FLOOR((MONTH(TODAY()) - 1) / 3) + 1

Why It’s Used:

  • Compact and logical - does basically the same thing as the prior example but with some more compact syntax

Pitfalls:

  • Hard to read and understand, not flexible
  • Has the same year negligence issue as the prior example

⭐ Best Practice Approach: Use CASE() to check the Year-Quarter

This formula works by converting both the current date and the target date into a standardized text format of “YYYYQ”, combining the year and quarter (e.g., “20253”). This is commonly referred to among insiders as the “year-quarter”.

It uses the TEXT() function to extract the year value, then the CASE() function to map each month to its corresponding quarter.

By comparing the resulting strings, it precisely determines if both the year and quarter match. This approach is clear, readable, and avoids the pitfalls of more complex math-based or month-by-month logic.

It’s the most reliable way to check if a date is in the same quarter of the same year as today—especially for validation and automation use cases. (Note: if you’re using the standard date grouping by fiscal quarter in a Salesforce report, it defaults to actually checking year-quarter in the backend.)

It’s also easily customizable to match non-standard fiscal quarter structures—as long as the quarters still align to calendar months (e.g., Q1 = Feb–Apr). You can update the CASE() logic to reflect your organization’s fiscal calendar. This makes it a highly flexible and robust solution for reporting, validation, and automation.

The only time when this formula breaks down is if your company doesn’t map fiscal quarters to the start of a month, e.g. if Q2 starts not on April 1, but on April 8th or some other in-between date. If this is what you need, go read this guide instead.

Example:

TEXT(YEAR(TODAY())) & 
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',
    'NA' // the default outcome will never happen because TODAY() always has a value
) = 
TEXT(YEAR(CloseDate)) & 
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',
    'NA' // the default outcome will never happen because CloseDate always has a value
)

Note on null-safe formulas:

If you’re referring to a custom date field which is not always populated, then the “else” of ‘NA’ could happen and that would need to be accounted for.

You can do this by nesting the whole formula inside an IF() statement, such as below, to make it null safe. This will return False if the CustomDate__c field is NULL, or if Custom_Date__c is not NULL but is not in the same quarter of today.

IF(
    CustomDate__c != NULL &&
    TEXT(YEAR(TODAY())) & 
    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',
        'NA' // the default outcome will never happen because TODAY() always has a value
    ) = 
    TEXT(YEAR(CustomDate__c)) & 
    CASE(
        MONTH(CustomDate__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',
        'NA' // CustomDate__c could be null, but if so the null check at the start of the formula would return false already, so this "else" of NA will never actually happen 
    ),
    TRUE,
    FALSE
)

Use Case Examples

1. Validation Rules

Ensure actions (e.g., updating an opportunity’s stage) only happen when a date is within the current quarter.

Example Validation Rule:

ISPICKVAL( StageName, 'Proposal' ) &&
ExampleField__c = NULL &&
Close_Date_in_Current_Quarter__c = TRUE

This validation rule will only require ExampleField__c at the Proposal stage IF the CloseDate is in the current quarter.

2. Automation

  • Only send an email alert for a won Opportunity if the close date is in the current quarter.

Conclusion

The CASE()-based formula for checking if a date falls in the current quarter—combined with the year using TEXT()—is the most efficient, readable, and flexible solution available.

While other methods might work in simple scenarios, they often introduce unnecessary complexity, risk performance issues, and become difficult to maintain, especially as your org grows and evolves.

This approach is not only robust and clean but also easily adaptable for different fiscal quarter structures, as long as they align with calendar months. With this technique, admins and developers can confidently build:

  • Accurate forecasts and pipeline dashboards
  • Reliable validation logic (e.g., required fields this quarter)
  • Time-sensitive automation (e.g., alerts based on current quarter close dates)

Outdated quarter-checking formulas can silently break business logic and mislead reporting. If you’re still relying on patchy workarounds, now’s the time to level up with a smarter, scalable solution.