Help

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

Solved
Jump to Solution
1757 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Visnja_Milidrag
6 - Interface Innovator
6 - Interface Innovator

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

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

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

@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

14 Replies 14

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

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

Revise to check for blanks:

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

Visnja_Milidrag
6 - Interface Innovator
6 - Interface Innovator

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

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

^ 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
6 - Interface Innovator
6 - Interface Innovator

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”

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

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

For some reason my post was flagged as problematic to the “community”, but I promise it’s just boring formula text.

I tried @augmented 's formula and it said something was wrong with it. I will play with it on Monday when I’m back to work…

I also tried the “plain english” → “formula” using the advice from @Kamille_Parks and came up with this – and grrrr. AT still doesn’t like it and says there’s something wrong:

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

In the formulas you write, make sure every parenthesis has a matching opening and closing one. For instance, you wrote BLANK) twice. It would always be BLANK(), not BLANK), not BLANK( and not BLANK.

Also your OR/YEAR statement doesn’t make sense. You’re trying to pass two values into YEAR() where you can only pass one. A proper statement there would be OR(YEAR({Pub Date}) > 2017, YEAR({Last Modified/Reviewed}) > 2017)

I don’t see anything wrong with @augmented’s formula.

kuovonne
18 - Pluto
18 - Pluto

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

Thanks @kuovonne, great catch on the curly quotes. They did transmogrify from something else when I took the formula to do search/replace to get the field names the original poster used.