Calculate the Fiscal Quarter of a Date in a Formula Field if the FY starts in Aug

Question

As the title suggests I'm trying to identify the fiscal quarter a CloseDate of an opportunity falls in, provided that the FY starts in Aug.

I have tried this as per document: https://help.salesforce.com/s/articleView?id=sf.formula_examples_dates.htm&type=5 which states that one can provide an offset, but the formula is not returning expected values.

The formula I've used is:

CEILING( ( MONTH ( date ) - 7 ) / 3)

But this is not only not returning positive values, but returning wrong values.

The expectation is that Aug, Sep, Oct would be 1, Nov, Dec, Jan be 2, Feb, Mar, Apr be 3 and May, Jun, Jul be 4.

But the results are all over the place.

Can you please help me get a formula for calculating the fiscal quarter when the FY starts in Aug?

Answer 1

Well, the obvious problem with this approach is that January would evaluate like
CEILING((1 - 7) / 3) => CEILING(-6/3) => -2

Simple subtraction isn't the right approach here.

Your actual goal here is to shift the calendar quarters by some amount. Quarters go in a cycle from 1 to 4. Whenever you start thinking about numbers "cycling", you should be thinking of taking the modulus.

Quarters, by definition, are modulo 4
You shift mathematical functions left-right on a graph when you add or subtract from the "X" value

So with that in mind, we get

MOD(
    /* The first month of a Quarter should be an integer */
    /* 8 + 4 = 12 and 12 / 3 = 4 (and not 4.33 or 4.67) */
    FLOOR( MONTH(Date) + 4 / 3 ),
    4
)
/* The modulus gets us 0-3, but we want 1-4 */
/* So just add 1 to the result of the MOD() function */

You also need to use FLOOR() here to get the appropriate result instead of CIELING(), because you want October to be in Q1 (given that August is the start of your "Q1").
(10 + 4) / 3 => 4.67
4 mod 4 + 1 = 1
5 mod 4 + 1 = 2

Answer 2

In addition to @Derek's approach, we can also try with CASE function.

Since Q1 corresponds to August i.e. 8th month , we need to subtract 7 to equalise it with January which is normally the start of Q1.

Months range from 1 to 12 , we need to take absolute value of it as we are subtracting 7 from it. We can just associate month numbers with respective quarters and use CASE function as below :

CASE(
    ABS(
        MONTH(
            My_date__c
        ) - 7
    ),
    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
)

Let's say someone needs the quarter to start from April , then they need to simply subtract 3 from the month like ABS(MONTH( My_date__c )-3). Rest of the mappings remain same.

Case function - https://help.salesforce.com/s/articleView?id=sf.customize_functions_case.htm&type=5