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!

 

[Dave87]

please post some sample data

 

[thisisliam]

Hi @Dave87 - I've attached a screenshot of a demo spreadsheet. I'm looking for a formula that will reference columns B, AD and AF as indicated in the first post.

 

[Fluff]

Hi & welcome to MrExcel.
Assuming the Dates in col B are always in order (oldest to newest) try

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

 

[kvsrinivasamurthy]

Try this

=INDEX($B$8:$B$250,AGGREGATE(14,6,ROW($AD$8:$AD$250)/(($AD$8:$AD$250="X")+($AF$8:$AF$250="X")),1)-ROW($B$8)+1)

 

[Fluff]

@kvsrinivasamurthy
If there are two Xs on the same row, then your formula will give an error, or an incorrect value.

 

[kvsrinivasamurthy]

Try this,it works.

=INDEX($B$8:$B$250,AGGREGATE(14,6,ROW($AD$8:$AD$250)/((($AD$8:$AD$250="X")+($AF$8:$AF$250="X"))>0),1)-ROW($B$8)+1)

 

[thisisliam]

Hi guys

Hi & welcome to MrExcel.
Assuming the Dates in col B are always in order (oldest to newest) try

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

Try this,it works.

=INDEX($B$8:$B$250,AGGREGATE(14,6,ROW($AD$8:$AD$250)/((($AD$8:$AD$250="X")+($AF$8:$AF$250="X"))>0),1)-ROW($B$8)+1)

Both formulas give the correct most recent date. And no, unless I'm sorting for a particular reason all dates are in order and the filter is always reset. In terms of adding 24 months to the final date is it best to just add 730 days to the end of the formula?

 

[Fluff]

Missed that bit, how about

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

 

[thisisliam]

Also, thanks so much!