Displaying Fiscal Years and Quarters

Meredith_Winner · ‎Nov 30, 2020

Hi there,
I have used the following formula based on a guide, however, I would like to use standard fiscal years/quarters and this formula is not accurate somewhere.

For instance, I have a date set in my table as 11/9/20 and it displays the quarter and fiscal year s Q1 FY’21 - the formula is below:

IF(OR(
FIND(01,DATETIME_FORMAT({Date},‘MM’)),
FIND(02,DATETIME_FORMAT({Date},‘MM’)),
FIND(03,DATETIME_FORMAT({Date},‘MM’))),“Q1”,

IF(OR(
FIND(04,DATETIME_FORMAT({Date},‘MM’)),
FIND(05,DATETIME_FORMAT({Date},‘MM’)),
FIND(06,DATETIME_FORMAT({Date},‘MM’))),“Q2”,

IF(OR(
FIND(07,DATETIME_FORMAT({Date},‘MM’)),
FIND(08,DATETIME_FORMAT({Date},‘MM’)),
FIND(09,DATETIME_FORMAT({Date},‘MM’))),“Q3”,

IF(OR(
FIND(10,DATETIME_FORMAT({Date},‘MM’)),
FIND(11,DATETIME_FORMAT({Date},‘MM’)),
FIND(12,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’))

Justin_Barrett · ‎Nov 30, 2020

I can help with part of this, at least for now. I haven’t done work with typical business accounting systems, so I’m not sure what “standard fiscal years/quarters” means. I know it’s offset from calendar years/quarters somehow, but my search online indicates that the offset can vary, so I’ll wait for your input before offering specific guidance.

Regarding the mis-assignment of November to Q1, though, that’s an easy fix. The problem is with what you’re passing to the FIND() function. Find can only find strings inside of other strings, but your FIND() functions are passing numbers. A quick test indicates that Airtable does convert the numbers to strings, but it’s dropping the leading zero (if present). That’s why it’s matching November with Q1: because 01 becomes “1” for the find, and “1” is definitely in “11”.

Thankfully the fix is easy: just wrap quotes around those numbers at the start of the FIND() functions to turn them into strings. That way it’ll look for “01”, “02”, “03”, etc.

With all that said, though, there’s an even easier way to do the comparison. Airtable has a MONTH() function that will return the month of a given date. Here’s the first part of the formula with that change applied:

IF(AND(MONTH(Date) >= 1, MONTH(Date) <= 3),"Q1",
IF(AND(MONTH(Date) >= 4, MONTH(Date) <= 6),"Q2",
IF(AND(MONTH(Date) >= 7, MONTH(Date) <= 9),"Q3",
IF(AND(MONTH(Date) >= 10, MONTH(Date) <= 12),"Q4"))))

Similar changes could be made elsewhere in your formula to use MONTH() instead of formatted dates that are turned back into numbers.

Meredith_Winner · ‎Nov 30, 2020

Hmm this also creates an error - For more context, I’m trying to create a column that says the quarter and the fiscal year: ie: Q1 FY '21

I’m defining the fiscal year as January 1- December 31 in a given year.

I inputted your formula and I now have one cell that has the Date written as 4/1/21 (which should be Q2 FY '21). This is correct in my old formula column, but with the new column with your formula states Q1.

Another error example: I have the date column 12/1/20 - with my formula is shows as Q1 - FY '21 (should be Q4 FY '20), with your formula is says Q1.

It looks as the formula you gave shows Q1 for everything.

Justin_Barrett · ‎Dec 02, 2020

Sorry. I was a bit hasty in my writing of that formula. All of the OR() functions should be AND() instead. I changed the formula above to reflect that. It will output the correct quarters now.

Also remember what I said above my formula (emphasis added):

I had intentionally only tacked the quarter indicator while waiting for your clarification re: the fiscal year.

Because you’re not offsetting the fiscal year from the calendar year, the latter part of the formula can also be greatly simplified. Here’s the full formula to create the output you seek:

IF(AND(MONTH(Date) >= 1, MONTH(Date) <= 3),"Q1",
IF(AND(MONTH(Date) >= 4, MONTH(Date) <= 6),"Q2",
IF(AND(MONTH(Date) >= 7, MONTH(Date) <= 9),"Q3",
IF(AND(MONTH(Date) >= 10, MONTH(Date) <= 12),"Q4"))))
& " FY '" & RIGHT(YEAR(Date) & "", 2)

Jessica_Crocker · ‎Jun 07, 2023

Thanks for this super useful thread/ formula! I have adapted it for UK financial quarters (Apr to Mar) - and thought I'd post below in case useful for others. I've started with year first so that it's easy to sort in the correct order.

IF(AND(MONTH({date}) >= 1, MONTH({date}) <= 3), (" FY '" & (RIGHT(YEAR({date})-1 & "", 2))), (" FY '" & RIGHT(YEAR({date}) & "", 2))) & "-" & IF(AND(MONTH({date}) >= 1, MONTH({date}) <= 3),"Q4",

IF(AND(MONTH({date}) >= 4, MONTH({date}) <= 6),"Q1",

IF(AND(MONTH({date}) >= 7, MONTH({date}) <= 9),"Q2",

IF(AND(MONTH({date}) >= 10, MONTH({date}) <= 12),"Q3"))))