Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Combine multiple lookup date fields

Solved
Jump to Solution
741 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Nicole_N
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I have a series of lookup fields that return multiple dates.  I'd like to combine those fields into one, listing all of the dates returned in M/D/YYYY.  I've messed with datetime_parse and format with a concatenate, but the formatting is either not M/D/YYYY or it only returns the first date listed in a series in the lookup field.

Thanks for any help!

1 Solution

Accepted Solutions
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
You can use something like this:

 

REPLACE(
  SUBSTITUTE(
    REGEX_REPLACE({lookup one}&{lookup two}&{third lookup},
    '(20\\d{2})-(\\d{2})-(\\d{2})', ', $2/$3/$1'),
 'T00:00:00.000Z', ''),
 1,2,'')

 

I suppose your years are >=2000.
if you have 19xx , try to change  20\\d{2} with \\d{4}

See Solution in Thread

3 Replies 3
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
You can use something like this:

 

REPLACE(
  SUBSTITUTE(
    REGEX_REPLACE({lookup one}&{lookup two}&{third lookup},
    '(20\\d{2})-(\\d{2})-(\\d{2})', ', $2/$3/$1'),
 'T00:00:00.000Z', ''),
 1,2,'')

 

I suppose your years are >=2000.
if you have 19xx , try to change  20\\d{2} with \\d{4}

Thank you!  That worked!  What would I need to do to have it be m/d/yyyy and not mm/dd/yyyy? Sorry, I'm not a coder (obviously)!

You mean remove zero if day or month less than 10? 
I suspect that task might have easier common solution, but here I'm just add another level of replace,
removing zero from {space)0 OR \0

REPLACE(
  SUBSTITUTE(
    REGEX_REPLACE(
      REGEX_REPLACE(start&end,
      '(20\\d{2})-(\\d{2})-(\\d{2})', ', $2/$3/$1'),
    '([ |/])0','$1'),
 'T00:00:00.000Z', ''),
 1,2,'')