Skip to main content

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!

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}

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

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,'')

Reply


Terms & Conditions