# Re: Nested IF Referencing Multiple Fields with String Return

Solved
621 0
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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’ )))

1 Solution

Accepted Solutions
16 - Uranus

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.

3 Replies 3
16 - Uranus

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.

6 - Interface Innovator

That worked! Thank you!

16 - Uranus

Great! Can you mark my reply as the solution?