Skip to main content

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.

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

)

Reply


Terms & Conditions