Skip to main content

Hi there - I’m looking to set up a formula to determine fiscal year and can’t figure it out. Help appreciated


I tried setting up an IF function (i.e., if the date in the “Engagement End Date” column is on or after 7/1/2018, then the fiscal year is 2019, and if the date in the “Engagement End Date” is on or after 7/1/2019, then fiscal year is 2020.)



Also - is there a formula in Airtable comparable to IFERROR? I don’t quite think ERROR and ISERROR formulas are what I’m looking for. Thanks!


Thanks!

You have IS_AFTER/IS_BEFORE functions: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#date_and_time_functions

Check here all the Date Formulas you can work with.


Hey Erika,

Little late to the party but thought I’d reply for the books as I just figured this out.


Essentially, you use DATETIME_FORMAT to get the month and calendar year as numerals and then a simple IF to return the calendar year if month is less than 7 (ie. it’s before July) or the calendar year plus one if it’s after July. That will work in perpetuity without having to manually define new ranges as the years pass. This looks like:


IF((DATETIME_FORMAT(Date, 'MM'))>6,((DATETIME_FORMAT(Date, 'YY'))+1), ((DATETIME_FORMAT(Date, 'YY'))))

Very late but took it a step further...this returns a Y if in current fiscal year, N if not..


IF(

IF((DATETIME_FORMAT(Date, 'MM'))>6,((DATETIME_FORMAT(Date, 'YYYY'))+1), ((DATETIME_FORMAT(Date, 'YYYY'))))=

IF((DATETIME_FORMAT(TODAY(), 'MM'))>6,((DATETIME_FORMAT(TODAY(), 'YYYY'))+1), ((DATETIME_FORMAT(TODAY(), 'YYYY')))),

"Y","N")

Reply


Terms & Conditions