Nested IF Formula for Dates in Hiring Table

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

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:

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

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.

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

IF(
    {Interview Date},
    etc.

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

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

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:

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

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.

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

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.

Hmmm… I wonder if @Jeremy_Oglesby could jump back in here. It seems like the following should work to append the “FY” before the year, but it throws a “NaN” in my test example and I don’t understand why.

IF(MONTH({1stPymtDate}) < 7, “FY” & YEAR({1stPymtDate})) & IF(MONTH({1stPymtDate}) > 6, “FY” & YEAR({1stPymtDate})+1)

For some reason, it works when I append the FY after the year, but not the other way around

IF(MONTH({1stPymtDate}) < 7, YEAR({1stPymtDate}) & “FY”) & IF(MONTH({1stPymtDate}) > 6, YEAR({1stPymtDate})+1 & “FY”)

When you append the “FY” after the year, you are separating concerns for Airtable saying first do the math (deal with the numbers), then convert it to a string and append(concatenate) the string “FY” to it.

The other way you tried is mixing strings into the math, which will throw the NaN error.

The way around is to use two fields to accomplish this - one field isolates the year (takes care of the math). Then in another field you take the result and concatenate it with “FY”.

This was exactly what I needed - thank you!!!