The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
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,'')