
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 27, 2022 09:01 AM
That worked! Thank you!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 27, 2022 10:12 AM
Great! Can you mark my reply as the solution?
