I’m looking to get the Nth oldest date from a list of text strings. The data looks like the following:
| Date |
|---|
| 100% (05/29/2024) |
| 100% (11/08/2022) |
| 100% (03/28/2022) |
| 100% (11/08/2022) |
| 100% (10/25/2022) |
| 0% |
| 25% (05/29/2024) |
| 100% (08/25/2022) |
| 100% (10/25/2022) |
The result, if N = 1, should be the oldest date or 03/28/2022. If N = 3 the result should be 10/05/2022.
I’m aware of the function SMALL() and MID() but I’m not sure how I can combine these two while also only looking for those fields that have dates. Only those that are >0% will have dates included, but only those at 100% should be evaluated.
>Solution :
Maybe try:
=IFERROR(INDEX(SORT(--REGEXEXTRACT(A1:A9,"^100% \((\d\d\/\d\d\/\d{4})\)$")),1),"No Value")
That way you’d only pull the dates when a string starts with ‘100%’ and follows the rest of the correct input’s pattern. Here the ‘1’ is your ‘N’. Just change it as desired.