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!
Best answer by Alexey_Gusev
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}
+12Hi,
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)!
+12Thank 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,'')Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
