Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 24, 2022 05:37 PM
I’m trying to figure out this formula, which I based on a different formula that I got to work successfully, but am hitting a wall.
Basically, I want the formula to reference 4 date fields (Edit Start, Format Start, Production Date/Time, and Ship Date).
If there are values in any of those fields, I want it to return, as a string, the date, and either "Edit Start: ", "Format Start: ", "Production Start: ", and/or "Ship Date: " ahead of each date, as applicable.
So, a full string with all dates filled in should look like: Edit Start: XX/XX/XX; Format Start: XX/XX/XX; Production Start: XX/XX/XX; Ship Date: XX/XX/XX
However, one or more of these date fields may be empty at any given time, so I want the text string to adjust accordingly and not show the lead-in if the corresponding date field is empty.
Here’s my attempt, which won’t compute.
“Edit Start: ” & DATETIME_FORMAT({Edit Start}, ‘L’ ) & IF(AND({Edit Start}, OR({Format Start}, {Production Date/Time}, {Ship Date})), "; ")
& “Format Start: ” & DATETIME_FORMAT({Format Start}, ‘L’ ) & IF(AND({Format Start}, OR({Production Date/Time}, {Ship Date})), "; ")
& “Production Start: ” & DATETIME_FORMAT({Production Date/Time}, ‘L’ ) & {Production Date/Time} & IF(AND({Ship Date})), "; ")
& (“Ship Date:” & DATETIME_FORMAT({Ship Date}, ‘L’ )))
Thank you in advance!
Solved! Go to Solution.
Jun 24, 2022 10:36 PM
Strategy: use IF()s to insert the prefix and date only if the relevant date field is filled in, end each date format with a semicolon and space. Then use REGEX to remove any semicolon followed by a space at the end of the line.
REGEX_REPLACE(CONCATENATE(
IF({Edit Start}, "Edit Start: " & DATETIME_FORMAT({Edit Start}, "L; ")),
IF({Format Start}, "Format Start: " & DATETIME_FORMAT({Format Start}, "L; ")),
IF({Production Date/Time}, "Production Date/Time: " & DATETIME_FORMAT({Production Date/Time}, "L; ")),
IF({Ship Date}, "Ship Date: " & DATETIME_FORMAT({Ship Date}, "L; "))
), "; $", ""
)
^ in REGEX, $ means “end of the line”. So any ;
at the end of the line will be replaced with nothing, or in other words it will be removed, and all other semicolons followed by spaces will stay in place.
Jun 24, 2022 10:36 PM
Strategy: use IF()s to insert the prefix and date only if the relevant date field is filled in, end each date format with a semicolon and space. Then use REGEX to remove any semicolon followed by a space at the end of the line.
REGEX_REPLACE(CONCATENATE(
IF({Edit Start}, "Edit Start: " & DATETIME_FORMAT({Edit Start}, "L; ")),
IF({Format Start}, "Format Start: " & DATETIME_FORMAT({Format Start}, "L; ")),
IF({Production Date/Time}, "Production Date/Time: " & DATETIME_FORMAT({Production Date/Time}, "L; ")),
IF({Ship Date}, "Ship Date: " & DATETIME_FORMAT({Ship Date}, "L; "))
), "; $", ""
)
^ in REGEX, $ means “end of the line”. So any ;
at the end of the line will be replaced with nothing, or in other words it will be removed, and all other semicolons followed by spaces will stay in place.
Jun 27, 2022 09:01 AM
That worked! Thank you!
Jun 27, 2022 10:12 AM
Great! Can you mark my reply as the solution?