Formula to determine fiscal year relative to date in other clumn

[Erika_Harano]

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!

[Elias_Gomez_Sai]

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

[Luiggi_Cuozzo]

Check here all the Date Formulas you can work with.

Airtable - Support

Formulas and date fields

If you would like to incorporate the data from a date-type field into a formula field, the best way to do this is by using the DATETIME_FORMAT function. (For more information on the different funct...

[Jackson_Scott]

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'))))

[kschnaenberg]

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")