Last date search in range

Bravo2003

Active Member
Not sure if this is possible.. but then again anything is possible in Excel I find from you lot...

I have a column of dates in Column C - 28/12/2018, 26/01/2019, 27/02/2019, 29/03/2019, etc...

In Cell C99 I need it to find the latest date related to the ref month number (1, 2, 3, 4, 5, etc - in cell A99) from the column range.

If that makes sense, is it possible?
 

jasonb75

Well-known Member
Given that you have dates from 2018 and 2019, you might need to specify year as well as month

For January 2019, try

=AGGREGATE(15,6,$C$2:$C$98/(MONTH($C$2:$C$98)=1)/(YEAR($C$2:$C$98)=2019),1)

Delete the part in bold if you don't need the year.
 

Bravo2003

Active Member
Given that you have dates from 2018 and 2019, you might need to specify year as well as month

For January 2019, try

=AGGREGATE(15,6,$C$2:$C$98/(MONTH($C$2:$C$98)=1)/(YEAR($C$2:$C$98)=2019),1)

Delete the part in bold if you don't need the year.
That gives a #NUM error.
Doesn't matter about year, as i will set the range criteria to just cover each years dates range - e.g. C2:C24 then C5:C55, etc..
 

Some videos you may like

This Week's Hot Topics

Top