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?

[augmented]

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

[Visnja_Milidrag]

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

[Kamille_Parks]

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 · Accepted solution]

augmented:

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

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

[augmented]

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.