Skip to main content

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!

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.

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.




That worked! Thank you!



That worked! Thank you!


Great! Can you mark my reply as the solution?

Reply


Terms & Conditions