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:
Unfortunately, the results come out as blank or “FY2013” only. Sorry if a similar topic has been resolved elsewhere 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.
Page 1 / 1
You’ll probably want to try using the IS_BEFORE() function rather than comparing dates with math operators.
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 . Here’s the erroneous formula that I entered:
Can you guide me on how to nest IS_BEFORE( )? :bowing_man:t4:
You’ll probably want to try using the IS_BEFORE() function rather than comparing dates with math operators.
Kicking this back up, can you provide any guidance on how to use IS_BEFORE() with a conditional IF formula?
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.
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.
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”
)
)
)
)
)
)
)
)
)
)
)
)
)
)
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:
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:
OMG thank you!!! t5: t5: :bowing_man:t5: Indeed, there was an extra closed parenthesis. :man_facepalming:t5:
Noted re: text editors, definitely utilizing that resource going forward.
OMG thank you!!! t5: 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.
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
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.
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.
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.
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.
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”.
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.
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
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.
. Here’s the erroneous formula that I entered:
t5: 
t5: :bowing_man:t5: Indeed, there was an extra closed parenthesis. :man_facepalming:t5: