Referencing Holiday String in a formula

[Nicole_Flewelle]

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!

[Kamille_Parks · Accepted solution]

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.

[Nicole_Flewelle]

Kamille_Parks:

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; "))
  ), "; $", ""
)

That worked! Thank you!

[Kamille_Parks]

Great! Can you mark my reply as the solution?