Skip to main content

Hi, I’m trying to figure out calculating correct fiscal year since our company fiscal year starts from April:
4-6: Q1
7-9: Q2
10-12: Q3
1-3: Q4
And I currently have below formula generating value from cell {ETA} but getting the YEARS correctly is tricky. Any ideas?

IF(OR(

FIND(04,DATETIME_FORMAT({ETA},‘MM’)),

FIND(05,DATETIME_FORMAT({ETA},‘MM’)),

FIND(06,DATETIME_FORMAT({ETA},‘MM’))),“Q1”,

IF(OR(

FIND(07,DATETIME_FORMAT({ETA},‘MM’)),

FIND(08,DATETIME_FORMAT({ETA},‘MM’)),

FIND(09,DATETIME_FORMAT({ETA},‘MM’))),“Q2”,

IF(OR(

FIND(10,DATETIME_FORMAT({ETA},‘MM’)),

FIND(11,DATETIME_FORMAT({ETA},‘MM’)),

FIND(12,DATETIME_FORMAT({ETA},‘MM’))),“Q3”,

IF(OR(

FIND(01,DATETIME_FORMAT({ETA},‘MM’)),

FIND(02,DATETIME_FORMAT({ETA},‘MM’)),

FIND(03,DATETIME_FORMAT({ETA},‘MM’))),“Q4”))))

&

IF(AND(VALUE(DATETIME_FORMAT({ETA},‘M’))>=6,

VALUE(DATETIME_FORMAT({ETA},‘M’))<=12)," - FY’"

&

DATETIME_FORMAT(DATEADD({ETA}, 1,‘year’),‘YY’)," - FY’"

& DATETIME_FORMAT({ETA},‘YY’))

Welcome to the Airtable community.

I suggest you use MONTH() and YEAR(). It makes the math easier.

IF(
    MONTH({ETA}) <= 3,
    "Q4 - FY" & RIGHT((YEAR({ETA}) - 1) & "", 2),
IF(
    MONTH({ETA}) <= 6,
    "Q1 - FY" & DATETIME_FORMAT({ETA}, "YY"),
IF(
    MONTH({ETA}) <= 9,
    "Q2 - FY" & DATETIME_FORMAT({ETA}, "YY"),
    "Q3 - FY" & DATETIME_FORMAT({ETA}, "YY")
)))

Welcome to the Airtable community.

I suggest you use MONTH() and YEAR(). It makes the math easier.

IF(
    MONTH({ETA}) <= 3,
    "Q4 - FY" & RIGHT((YEAR({ETA}) - 1) & "", 2),
IF(
    MONTH({ETA}) <= 6,
    "Q1 - FY" & DATETIME_FORMAT({ETA}, "YY"),
IF(
    MONTH({ETA}) <= 9,
    "Q2 - FY" & DATETIME_FORMAT({ETA}, "YY"),
    "Q3 - FY" & DATETIME_FORMAT({ETA}, "YY")
)))

This worked perfectly.
Thank you!

Terms & ConditionsAccessibility statement