All posts
RevOps

Comparing date fields across quarters with formulas

If your org follows a standard calendar fiscal year, you can use simple month-to-quarter logic to determine whether a given date is in a past, current, or…

Revenue OperationsSalesforce

If your org follows a standard calendar fiscal year, you can use simple month-to-quarter logic to determine whether a given date is in a past, current, or future quarter compared to today.

This is useful for:

  • Flagging upcoming or overdue milestones
  • Filtering dashboard components based on timing
  • Driving conditional logic for visibility or notifications

Step 1: Return a Sortable Year-Quarter Value from Any Date Field

We’ll first map the month to a quarter (1–4), and then combine that with the year to create a sortable text value like “20242”.

Formula: Year-Quarter of any date field

TEXT(YEAR(MyDate__c)) &
CASE(
  MONTH(MyDate__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",
  "0" /* fallback, shouldn't happen */
)

This formula returns the year-quarter of MyDate__c date field, and aligns to a standard fiscal calendar. The Case method could be adjusted for any fiscal calendar as long as each quarter begins on the 1st of the month. If the fiscal quarter begins on a non-first day of month, that requires a more complex solution you can read about here.

Step 2: Year-Quarter of the Current Quarter

Use the same type of logic to calculate the current YearQuarter for today…

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",
  "0"
)

Step 3: Compare the date field and see if it is greater than or less than the current quarter

You can now use a comparison formula to return a result in different formats. One option is to return True if the date is in a later or future quarter if that meets the requirement.

This formula returns True if the date field is in a later quarter than current quarter:

/* Returns TRUE if date is in a later quarter than today */
TEXT(YEAR(MyDate__c)) &
CASE(
  MONTH(MyDate__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",
  "0"
)
>
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",
  "0"
)

But you can also use a formula to return a text value indicating what the result is, such as the below which returns “Earlier quarter”, “Later quarter”, or “Same quarter” as an output of the comparison:

/* Create a sortable Year+Quarter string for MyDate__c */
CASE(
  TEXT(YEAR(MyDate__c)) &
  CASE(
    MONTH(MyDate__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",
    "0"
  ),

  /* Compare to TODAY’s Year+Quarter string */
  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",
    "0"
  ), "Same quarter",

  /* If less than today, it's an earlier quarter */
  CASE(
    TRUE,
    TEXT(YEAR(MyDate__c)) &
    CASE(
      MONTH(MyDate__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",
      "0"
    )
    <
    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",
      "0"
    ), "Earlier quarter",

    /* Else it must be later */
    "Later quarter"
  )
)

Why This Works

By converting the year and quarter into a single text value (like “20242”), you get a sortable representation that allows for simple string comparisons using <, >, or =. This approach is easy to read, easy to maintain, and requires no extra fields or metadata.