Output most recent date Excel

Question

I'm looking to derive a formula which will output the most recent date between cells CK:CM. My sheet looks like this:

For row 2, output should be 2021-06-04, row 4 should be 2021-05-06, row 5 should be N/A.

I'm currently using a nested IF statement within a MAX function which returns 00 January 1900 for all rows: =MAX(IF('Loan Tape'!B2:B51=Data!B2, 'Loan Tape'!CK2:CM51))

Answer 1

I'm a little confused and not sure how column B is relevant in your example formula since you didn't mention it (or your IF condition) in your explanation.

So outside of of that, if just want the most recent date, for example, for CK4:CM4, then you'd simply use =MAX(CK4:CM4).

This works fine even with the rows that have a cell containing the text N/A. It's only an issue when all 3 date cells for that row are 'N/A', in which case it will return 0 (which, if formatted as a date, displays as 1900-01-01) but a simple IF will deal with that, like for row 5:

=IF(MAX(CK5:CM5)=0,"unknown",MAX(CK5:CM5))

...replacing unknown with whatever you want it to return in that case.

If you meant that you needed the most recent date out of all rows for those 3 columns, you'd use =MAX(CK:CM) which returns 2021-06-04 using your sample data.