Get the latest 3 dates

Question

I have a sheet with a status report date.  Is there a way to search for the latest 3 dates?

Example: I want to return the rows for dates 11/06/20, 10/30/20, 10/23/20.  But the dates could be any date values, just want to get latest 3.

I thought of trying to convert date to serial number and just max function, but don't see a way of converting the date to serial number like you can in excel.

Dale Murphy · Accepted Answer

@Paulo Nunes Try the LARGE function

Here, Column30 holds a list of dates, column42 holds the rank value (1 to 3 or higher)

=LARGE([Column30]:[Column30], [Column42]@row) where column 42 @ row = 1

=LARGE([Column30]:[Column30], [Column42]@row) where column 42 @ row = 2

=LARGE([Column30]:[Column30], [Column42]@row) where column 42 @ row = 3

The column you put this formula in has to be a DATE column itself.

Cheers,

Dale

Paulo Nunes · Accepted Answer

Thanks Dale. Your recommendation provides me a good answer to another situation I'm trying to figure out.

For the one above, I needed to apply the rank values on each of the rows.  I ended up using a helper field (report age) and then found a function that ranks numeric values among other values in a range.

This allows us to alway pull the top 3 (latest 3) updates on a sheet regardless if the dates are evenly set apart or not.