Get the latest 3 dates

Paulo Nunes

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

@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

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.

Dale Murphy

@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

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.