Calculate latest date from two columns

[thisisliam]

Hi all

I need to calculate the latest date of two columns and add two years to that date.

Column B contains the date (B8:B250 to exclude headers). Columns AD (AD8:AD250) and AF (AF8:AF250) are cells which are used to define what specific event, if any, was logged on that date, denoted by a simple "X" value entered by the user. I need to calculate the latest date, if any, defined by an "X" value in either columns AD or AF and add two years.

For example, this spreadsheet is a pilot logbook. Column AD is where the user would indicate if that entry was a Checkride in the logbook, AF is where the user would indicate if that entry was a Flight Review in the logbook. A Flight Review is required two years after the last logged Flight Review OR after the last Checkride. If the pilot has a Checkride within that time then that resets the two year date as a Checkride can be considered a Flight Review but a Flight Review cannot be considered a Checkride.

The formula needs to calculate the date of the next Flight Review.

Any help greatly appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[thisisliam]

Missed that bit, how about

=EDATE(LOOKUP(2,1/(((AD8:AD250="X")+(AF8:AF250="X"))>0),B8:B250),24)

EDATE() solved that one. Thanks guys. You've helped me add another feature to my really slick logbook :D