Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 21, 2024 03:25 PM
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!
Solved! Go to Solution.
May 21, 2024 06:30 PM - edited May 21, 2024 06:34 PM
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}
May 21, 2024 06:30 PM - edited May 21, 2024 06:34 PM
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}
May 23, 2024 10:55 AM
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)!
May 25, 2024 08:51 AM
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,'')