Displaying quarters in a fiscal year
  • 06 Jul 2022
  • 1 Minute to read
  • Dark
    Light

Displaying quarters in a fiscal year

  • Dark
    Light

You can use a formula to display the quarter of a fiscal year based on the date in another field, even if your fiscal year is different from the calendar year.

As an example, let's say your fiscal year runs from June through May; that means your first quarter (Q1) of the year would start on June 1. You can build a formula to output the correct quarter and fiscal year based on an existing date field.

Step 1

To start, one formula field will be used to output just the month (based on the date field) using the formula below:

IF({Date},DATETIME_FORMAT({Date},'MM'))

Here's how that will appear:

Screen Shot 2019-08-20 at 1.56.19 PM

Step 2

Once that's configured, a second formula will reference the Month field, and generate the rest:

IF(OR(FIND(06,{Month}),FIND(07,{Month}),FIND(08,{Month})),"Q1",
IF(OR(FIND(09,{Month}),FIND(10,{Month}),FIND(11,{Month})),"Q2",
IF(OR(FIND(12,{Month}),FIND(01,{Month}),FIND(02,{Month})),"Q3",
IF(OR(FIND(03,{Month}),FIND(04,{Month}),FIND(05,{Month})),"Q4"))))

& 

IF(AND(VALUE(DATETIME_FORMAT({Date},'M'))>=6,VALUE(DATETIME_FORMAT({Date},'M'))<=12)," - FY'" 

& 

DATETIME_FORMAT(DATEADD({Date}, 1,'year'),'YY')," - FY'" & DATETIME_FORMAT({Date},'YY'))

This will output and format the quarter and fiscal year:
Screen Shot 2019-08-20 at 1.57.19 PM

Step 3 (optional)

If you'd prefer to not use two formula fields to accomplish this, you can combine the two previous formula fields into a single one. Here's how that formula would look all together.

IF(OR(
FIND(06,DATETIME_FORMAT({Date},'MM')),
FIND(07,DATETIME_FORMAT({Date},'MM')),
FIND(08,DATETIME_FORMAT({Date},'MM'))),"Q1", 

IF(OR(
FIND(09,DATETIME_FORMAT({Date},'MM')),
FIND(10,DATETIME_FORMAT({Date},'MM')),
FIND(11,DATETIME_FORMAT({Date},'MM'))),"Q2", 

IF(OR(
FIND(12,DATETIME_FORMAT({Date},'MM')),
FIND(01,DATETIME_FORMAT({Date},'MM')),
FIND(02,DATETIME_FORMAT({Date},'MM'))),"Q3", 

IF(OR(
FIND(03,DATETIME_FORMAT({Date},'MM')),
FIND(04,DATETIME_FORMAT({Date},'MM')),
FIND(05,DATETIME_FORMAT({Date},'MM'))),"Q4")))) 

& 

IF(AND(VALUE(DATETIME_FORMAT({Date},'M'))>=6,
VALUE(DATETIME_FORMAT({Date},'M'))<=12)," - FY'" 

& 

DATETIME_FORMAT(DATEADD({Date}, 1,'year'),'YY')," - FY'" 
& DATETIME_FORMAT({Date},'YY'))

Screen Shot 2019-08-20 at 1.57.29 PM


Was this article helpful?