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!

Reply


Terms & Conditions