Search code examples
google-sheetsgoogle-sheets-formula

Finding Nth earliest (oldest) date from list, date is partial string

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.