Re: Formula to output the later year of two date fields?

Visnja_Milidrag · ‎Jan 22, 2021

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?

kuovonne · ‎Jan 22, 2021

@augmented’s formula looks great to me, except some curly quotes snuck in. Those curly quotes are probably the problem. Here is the formula again with the formula written on multiple lines and the curly quotes replaced with straight quotes, and the final empty string removed.

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

Here’s another possibility. I personally prefer @augmented’s formula, but you should use whatever formula produces the result you want and will be easy for you to maintain.

Here is the logic of this formula:

If both fields have values, and at least one is after 2017, return the larger year,
If both fields have values, but neither are before 2017, return “2017 or older”.
If only one field has a value, and it is after 2017, return that year.
If only one field has a value, but it is 2017 or earlier, return “2017 or older”.
If both date fields are blank, the result is blank.

Note that the formula result is always string (never a number), because you want to include the string “2017 or older”.

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

See Solution in Thread

Kamille_Parks · ‎Jan 22, 2021

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 · ‎Jan 22, 2021

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 · ‎Jan 22, 2021

Revise to check for blanks:

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

Visnja_Milidrag · ‎Jan 22, 2021

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 · ‎Jan 22, 2021

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 · ‎Jan 22, 2021

^ 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 · ‎Jan 22, 2021

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 · ‎Jan 22, 2021

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 · ‎Jan 22, 2021

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