Nested IF Formula for Dates in Hiring Table


#1

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.


#2

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


#3

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: