Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Extract date from list of dates

Solved
Jump to Solution
182 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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()
)

See Solution in Thread

1 Reply 1

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()
)