Search code examples
exceldatefiscal

Relative fiscal quarter calculation formula

Requesting guidance calculating a field in Excel, called Relative Fiscal Quarter. This would be a numeric field containing positive/negative integers (no decimals). This field will be based on dates field which has dates from Jan 1, 2009 to Dec 31, 2037.

Dataset Fields Excel Column Formula
dates A None
Fiscal Year B =IF(MONTH([@dates])>=11, YEAR([@dates])+1, YEAR([@dates]))
Fiscal Quarter C =IF(MONTH([@dates])>=11,CEILING((MONTH([@dates])-10)/3,1), CEILING((MONTH([@dates])+2)/3,1))
Today's Date D =TODAY()
Today's Date Fiscal Year E =IF(MONTH(TODAY())>=11,YEAR(TODAY())+1,YEAR(TODAY()))
Today's Date Fiscal Quarter F =IF(MONTH(TODAY()) >= 11, CEILING((MONTH(TODAY()) - 10) / 3, 1), CEILING((MONTH(TODAY()) + 2) / 3, 1))
Current Fiscal Quarter G =IF([@[Fiscal Year]]=[@[Todays Date Fiscal Year]],IF([@[Fiscal Quarter]]=[@[Todays Date Fiscal Quarter]],TRUE,FALSE))
Relative Fiscal Quarter (Desired Output) F ?

Sample data:

enter image description here

For this use case, the fiscal quarter begins in November.

Fiscal Qtr. Months
2023-FQ1 Nov, Dec, Jan
2023-FQ2 Feb, Mar, Apr
2023-FQ3 May, Jun, Jul
2023-FQ4 Aug, Sep, Oct

Relative Fiscal Quarter should be 0 for all dates that are the current fiscal quarter, -1 for the prior fiscal quarter and so on. Additionally, the field should be 1,2,3... for future fiscal quarters. Value of Relative Fiscal Quarter and Current Fiscal Quarter will change as fiscal quarters pass.

Attempted to calculate the Relative Fiscal Quarter using this formula, but as you can see it only partially succeeds.

=IF(\[@\[Current Fiscal Quarter\]\], 0, (\[@\[Fiscal Year\]\] - \[@\[Todays Date Fiscal Year\]\]) \* 4 + IF(MONTH(\[@dates\]) \>= 11, CEILING((MONTH(\[@dates\]) - 10) / 3, 1), CEILING((MONTH(\[@dates\]) + 2) / 3, 1))) - (\[@\[Fiscal Year\]\] -\[@\[Todays Date Fiscal Year\]\]) \* 4

Sample output: enter image description here

Solution

  • Keeping with your sample table structure, the following function should work in the Relative Fiscal Quarter column:

    =([@[Fiscal Year]]-[@[Todays Date Fiscal Year]])*4+([@[Fiscal Quarter]]-[@[Todays Date Fiscal Quarter]])