Skip to main content

Formula to determine fiscal year relative to date in other clumn

  • July 30, 2018
  • 4 replies
  • 64 views
E

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!

    4 replies

    E
    Forum|alt.badge.img+17

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

    L
    Forum|alt.badge.img+2
    • Luiggi_Cuozzo
    • Participating Frequently
    • July 30, 2018

    Check here all the Date Formulas you can work with.

    J
    Forum|alt.badge.img
    • Jackson_Scott
    • New Participant
    • January 11, 2021

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

    K
    Forum|alt.badge.img+8
    • kschnaenberg
    • Inspiring
    • October 24, 2024

    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")
    Terms & ConditionsAccessibility statement