Google Sheets筛选公式问题：提取文本中符合日期范围的数据

Core Issue with Your Current Formula #

Your formula uses ISNUMBER(SEARCH(TEXT(E2,"mm/dd/yyyy"), C2:C7)) and ISNUMBER(SEARCH(TEXT(F2,"mm/dd/yyyy"), C2:C7)), which requires both the start date (E2) and end date (F2) to appear as exact substrings in the text of C2:C7. This is why your target row (with 4/4/24) isn’t showing up—neither 4/2/24 nor 4/7/24 are present in that cell’s text.

Corrected Formula #

Instead of checking for the presence of the start/end dates, we need to extract the date from the text in C2:C7, convert it to a valid date value, then verify if it falls within your specified range (G2 to H2):

=IFNA(UNIQUE(FILTER({A2:A7,B2:B7,C2:C7}, 
  (A2:A7=E2)*
  (D2:D7=F2)*
  ISNUMBER(IFERROR(DATEVALUE(REGEXEXTRACT(C2:C7, "\d{1,2}/\d{1,2}/\d{2}")), 0))*
  (DATEVALUE(REGEXEXTRACT(C2:C7, "\d{1,2}/\d{1,2}/\d{2}")) >= G2)*
  (DATEVALUE(REGEXEXTRACT(C2:C7, "\d{1,2}/\d{1,2}/\d{2}")) <= H2)
)), "")

Breakdown of the Corrected Formula #

• REGEXEXTRACT(C2:C7, "\d{1,2}/\d{1,2}/\d{2}"): Extracts the first date string in the format mm/dd/yy from each cell in C2:C7.
• DATEVALUE(...): Converts the extracted date string into a numerical date value that Google Sheets can compare.
• ISNUMBER(IFERROR(...)): Ensures we only include rows where a valid date was found in C2:C7 (excludes rows with no date or invalid date formats).
• The range checks (>= G2 and <= H2) verify if the extracted date falls within your desired start and end dates.
• The remaining conditions (A2:A7=E2 and D2:D7=F2) keep your original filtering requirements intact.

Handling Multiple Dates in a Single Cell #

If some cells in C2:C7 contain multiple dates and you want to include rows where any of the dates fall within the range, use this modified formula with BYROW and COUNTIF:

=IFNA(UNIQUE(FILTER({A2:A7,B2:B7,C2:C7}, 
  (A2:A7=E2)*
  (D2:D7=F2)*
  BYROW(C2:C7, LAMBDA(cell, 
    COUNTIF(ARRAYFORMULA(IFERROR(DATEVALUE(REGEXEXTRACT(SPLIT(cell, " "), "\d{1,2}/\d{1,2}/\d{2}"))), 
    ">="&G2, "<="&H2) > 0)
))), "")

This splits the cell text by spaces, extracts all dates, converts them, and checks if at least one falls within the range.

内容的提问来源于stack exchange，提问作者Kolev_I_N