Formula to output the later year of two date fields?

[Visnja_Milidrag]

I’m trying to have a field formula yield the later year found between two date fields (even if one of the fields is blank). I’m completely confused how to add a IS_BEFORE function into the IF(OR) statement…

The fields I’m working with is: {Last Modified/Reviewed} and {Date Pub}. I want a third column to return the YYYY that is later of those two (or the only one not empty of the two), and if the year is before 2018, to give me the label “2017 or older”…

Formula to output the later YYYY of two date fields Formula to output the later year of two date fields?

[Kamille_Parks]

Seems like you could just do:

IF(
   MAX(YEAR({Last Modified/Reviewed}), YEAR({Date Pub})) > 2017,
   MAX(YEAR({Last Modified/Reviewed}), YEAR({Date Pub})),
   "2017 or older"
)

[Visnja_Milidrag]

Ah yes - I am starting to get my brain around the logic…!! Thank you!

However, if one of the date fields is blank, it doesn’t give me anything.

Tried this, but I can tell it’s wrong – not sure how to add another OR condition…

IF(OR(MAX(YEAR({Last Modified/Reviewed}), YEAR({Pub Date}))>2017,FIND(OR({Last Modified/Reviewed}=BLANK),{Pub Date})=BLANK)),MAX(YEAR({Pub Date}),({Last Modified/Reviewed}), “2017 and older”)

[Kamille_Parks]

Revise to check for blanks:

IF(AND({Last Modified/Reviewed}, {Date Pub}), [insert formula I gave before], BLANK())

[Visnja_Milidrag]

Hmm… for some reason that’s not working. The earlier formula (Without checking for blanks), would give me the false output “2017 and older” for any rows where one of the dates are blank.

I wonder, is there a way to state this formula so that i tell it, IF Mod date is blank, give me YEAR of Pub date, IF Pub date is blank, give me YEAR of Mod date, otherwise (your formula)

How on earth do I string that together? Thank you for your help! You are a wiz at this. My brain doesn’t compute this too good…)

[Visnja_Milidrag]

i.e. something like… (so you can see that I’m actually trying lol)

IF(OR{Last Modified/Reviewed}=BLANK,YEAR{Pub Date}),{Pub Date}=BLANK, YEAR({Last Modified/Reviewed})),IF(
MAX(YEAR({Last Modified/Reviewed}), YEAR({Pub Date})) > 2017,
MAX(YEAR({Last Modified/Reviewed}), YEAR({Pub Date}),
“2017 or older”)

[Kamille_Parks]

^ the formula you gave is missing parenthesis in several places, and you’re not using OR() properly. OR() asks “are any of these arguments true, not blank, or not an error”. Assuming you added parenthesis where they belong, that OR() statement would always return true because you gave it all possible outcomes (in other words, you’ve asked “is anything possible?”, the answer is “yes”)

Using the formula I gave, including the parts which check for blank fields, works for me. When you say “its not working”, what do you mean?

[Visnja_Milidrag]

When I input the formula you provided that checks for blanks, the formula yields nothing for records where the Last Reviewed Date is blank, but has a Pub Date.
I’m thinking it needs a revision to allow for one or the other being blank but not sure how to logically build that…

IF(AND({Last Modified/Reviewed}, {Pub Date}), IF(
MAX(YEAR({Last Modified/Reviewed}), YEAR({Date Pub})) > 2017,
MAX(YEAR({Last Modified/Reviewed}), YEAR({Date Pub})),
“2017 or older”
), BLANK())

IF A is blank, give me Year of B if B is blank give me Year of A, otherwise give me the max Year between A and B if > 2017, otherwise “2017 or older”

[Kamille_Parks]

You’ve written out the logic you want in plain English, just turn that into a formula. 9/10 writing a nested IF() statement in Airtable means taking the third “argument” of an IF() statement and replacing it with another IF() statement.

IF([something], [then do this], IF([something else], [then do this], IF([yet another thing], [then do this], [do this if no conditions were met])))

[augmented]

It looks worse than it is :slightly_smiling_face:

IF(OR({Last Modified/Reviewed},{Pub Date}),IF(MAX(IF({Pub Date},YEAR({Pub Date}),0),IF({Last Modified/Reviewed},YEAR({Last Modified/Reviewed}),0))>2017,MAX(IF({Pub Date},YEAR({Pub Date}),0),IF({Last Modified/Reviewed},YEAR({Last Modified/Reviewed}),0)),“2017 or older”),"")