Nested IF Formula for Dates in Hiring Table

[Gaspar_Caro]

I’m switching my nonprofit program from Excel and so far love it. Our employment program’s base tracks participant outcomes – referral, interview, offer, initial hire, and retention – in a hiring pipeline among the many companies that we partner with and report them on a fiscal-year cycle of July 1 - June 30.

I want to automatically sort the “interviews” into FY buckets but am having trouble properly writing a formula to return the right values in an “Interview Cycle” field:

IF({Interview Date} = “”,"", IF({Interview Date} <= 6/30/2013,“FY2013”,IF({Interview Date} <= 6/30/2014,“FY2014”,IF({Interview Date} <= 6/30/2015,“FY2015”,IF({Interview Date} <= 6/30/2016,“FY2016”,IF({Interview Date} <= 6/30/2017,“FY2017”,IF({Interview Date} <= 6/30/2018,“FY2018”)))))))

Unfortunately, the results come out as blank or “FY2013” only. Sorry if a similar topic has been resolved elsewhere :grimacing: and thank you in advance to anyone who can point me in the right direction! The hope is that we can duplicate the formula for the other interactions too and will save us time.

[Jeremy_Oglesby]

You’ll probably want to try using the IS_BEFORE() function rather than comparing dates with math operators.

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

[Gaspar_Caro]

Thanks so much for your reply and regular support on the forum. Sadly, I don’t know how to use the IS_BEFORE( ) function in a Nested IF Formula but gave it a shot and came back with a check formula warning :disappointed: . Here’s the erroneous formula that I entered:

IF({Interview Date} = “”, “”, IF(IS_BEFORE({Interview Date}, 6/30/2013), “FY2013”, IF(IS_BEFORE({Interview Date}, 6/30/2014), “FY2014”, IF(IS_BEFORE({Interview Date}, 6/30/2015), “FY2015”, IF(IS_BEFORE({Interview Date}, 6/30/2016), “FY2016”, IF(IS_BEFORE({Interview Date}, 6/30/2017), “FY2017”, IF(IS_BEFORE({Interview Date}, 6/30/2018), "FY2018”,“Edit Date”)))))))

Can you guide me on how to nest IS_BEFORE( )? :bowing_man:t4:

[Gaspar_Caro]

Kicking this back up, can you provide any guidance on how to use IS_BEFORE() with a conditional IF formula?

[W_Vann_Hall]

Your nested IF() statement is fine. (Personally, I’d change the opening from

IF(
    {Interview Date} = '',
    '',
    etc.

to

IF(
    {Interview Date},
    etc.

but that’s more personal preference than anything else.)

Your problem lies in your IS_BEFORE() statements. IS_BEFORE() takes as arguments two datetime values — and 6/30/2014 is not a datetime value. Do something like this instead:

IS_BEFORE(
    {Interview Date},
    DATETIME_PARSE(
        '6/30/2014',
        'M/D/YYYY'
        )
    )

Replace each of your non-date dates with a similar construction, and it should work.

[Gaspar_Caro]

Thank you for the recommendation, unfortunately I came back with another Invalid Formula error due to my amateurish efforts. Hopefully this chain is helpful for other members in the future!

Last failed formula, I also tried it with “MM/DD/YYYY” and had no luck:

IF({Interview Date},
IF(IS_BEFORE(
{Interview Date}, DATETIME_PARSE(
“6/30/2013”,“M/D/YYYY”
), “FY2013”, IF(
IS_BEFORE(
{Interview Date}, DATETIME_PARSE(
“6/30/2014”,“M/D/YYYY”
), “FY2014”, IF(
IS_BEFORE(
{Interview Date}, DATETIME_PARSE(
“6/30/2015”,“M/D/YYYY”
), “FY2015”, IF(
IS_BEFORE(
{Interview Date}, DATETIME_PARSE(
“6/30/2016”,“M/D/YYYY”
), “FY2016”, IF(
IS_BEFORE(
{Interview Date}, DATETIME_PARSE(
“6/30/2015”,“M/D/YYYY”
), “FY2017”, IF(
IS_BEFORE(
{Interview Date}, DATETIME_PARSE(
“6/30/2018”,“M/D/YYYY”
), "FY2018”,“Edit Date”
)
)
)
)
)
)
)
)
)
)
)
)
)
)

[Jeremy_Oglesby]

Try this:

IF(
  {Interview Date},
  IF(
    IS_BEFORE(
      {Interview Date},
      DATETIME_PARSE(
        '6/30/2013',
        'M/D/YYYY'
      )
    ),
    "FY2013",
    IF(
      IS_BEFORE(
        {Interview Date},
        DATETIME_PARSE(
          '6/30/2014',
          'M/D/YYYY'
        )
      ),
      "FY2014",
      IF(
        IS_BEFORE(
          {Interview Date},
          DATETIME_PARSE(
            '6/30/2015',
            'M/D/YYYY'
          )
        ),
        "FY2015",
        IF(
          IS_BEFORE(
            {Interview Date},
            DATETIME_PARSE(
              '6/30/2016',
              'M/D/YYYY'
            )
          ),
          "FY2016",
          IF(
            IS_BEFORE(
              {Interview Date},
              DATETIME_PARSE(
                '6/30/2017',
                'M/D/YYYY'
              )
            ),
            "FY2017",
            IF(
              IS_BEFORE(
                {Interview Date},
                DATETIME_PARSE(
                  '6/30/2018',
                  'M/D/YYYY'
                )
              ),
              "FY2018"
            )
          )
        )
      )
    )
  )
)

I think using the double quotes inside of the DATETIME_PARSE() functions was not helping, and you also had some misplaced parenthesis.

FWIW, it’s much, much easier to write these big, hairy nested formulas in an external editor, rather than in the Airtable interface, and then copy them into Airtable. There are a lot of good, free text-editors out there, like notepad++, visual studio code, atom, etc. I suggest looking one up and giving it a try.

Here’s my work in VS Code for reference:

[Gaspar_Caro]

Gaspar_Caro:

(

OMG thank you!!! :clap:t5: :raised_hands:t5: :bowing_man:t5: Indeed, there was an extra closed parenthesis. :man_facepalming:t5:

Noted re: text editors, definitely utilizing that resource going forward.

[Arlo_Haskell]

Hey @Gaspar_Caro, your need to correspond to fiscal year caught my eye. Jeremy’s formula is great but the dates you provided only cover the period FY2013-FY2018. You’ll have to update the formula each year or build it now with as many fiscal years as you want the system to work for. I think you might actually be able to simplify this a great deal and make it permanent with math operators that calculate the FY based on what month you are in.

Try this:

IF(MONTH({Interview Date}) < 7, YEAR({Interview Date}))
& IF(MONTH({Interview Date}) > 6, YEAR({Interview Date})+1)

I’ve assumed the following: your fiscal year is July 1-June 30; if the interview is dated June 30, 2018, you want it in FY 2018; and if July 1, 2018, you want it in FY2019

[Gaspar_Caro]

That’s right @Arlo_Haskell you assumed correctly, my program annually reports on a July 1- June 30 fiscal year cycle. I had already updated the formula to include FY2019 so thank you for the simpler formula where my team won’t need to do that going forward. How could I append or concatenate “FY” before the year?

I copied the formula over to a “Hire Cycle” field that returns the proper fiscal year result based on {Job Start Date} so the simpler formula will actually streamline both of these functions.

Thanks again @Jeremy_Oglesby and @W_Vann_Hall for orienting me and getting on the right track with the date operators. This chain has been VERY helpful and informative, hopefully will be for others in the future too.