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.

[Arlo_Haskell]

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

[Jeremy_Oglesby]

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

[Jeannie_Ruesch_]

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