Extract date from list of dates

[Ambroise_Dhenai]

I have a field that contains either a single date, or a list of dates separated by comma.

2022-08-21
2023-05-01, 2023-06-01

I want to create two formulas:

• One that extracts the first date and compare it to another
• One that extracts the second date and compare it to another

To do so, I first need to extract the date, and that’s where I’m stuck at the moment.

I tried

IF(
  REGEX_MATCH(dates & '', '^(\\d{4}-)(\\d{2}-)(\\d{2})'),
  REGEX_EXTRACT(dates & '', '^(\\d{4}-)(\\d{2}-)(\\d{2})'),
  BLANK()
)

But I don’t get the expected result:
2023-05-01, 2023-06-01 > returns 2023- (expected 2023-05-01)

I used Regex101 which made me assume this would work fine, I also checked on the forum and tried many variations but I don’t get it.

[Ambroise_Dhenai · Accepted solution]

Ahhh, I managed to make it work:

IF(
  REGEX_MATCH(dates & '', '^(\\d{4}-\\d{2}-\\d{2})'),
  REGEX_EXTRACT(dates & '', '^(\\d{4}-\\d{2}-\\d{2})'),
  BLANK()
)

IF(
  REGEX_MATCH(dates & '', '(, \\d{4}-\\d{2}-\\d{2})'),
  SUBSTITUTE(REGEX_EXTRACT(dates & '', '(, \\d{4}-\\d{2}-\\d{2})'), ', ', ''),
  BLANK()
)