Help

SUBSTITUTE error when value doesn't exist

Topic Labels: Formulas
Solved
Jump to Solution
2102 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Jess_Helmstette
6 - Interface Innovator
6 - Interface Innovator

Hello!

I have an issue with a SUBSTITUTE formula I am using. I started with the following formula:

SUBSTITUTE(
    SUBSTITUTE(
      {Email Body} & "",
        "{Appraisal Contingency Date}",{Appraisal Contingency Date} & ""),
        "{Conditional Approval Due Date}",{Conditional Approval Due Date} & "")

It returns the following:
Appraisal Contingency Expires: 2022-09-20T00:00:00.000Z
Conditional Loan Approval Due: 2022-09-02T00:00:00.000Z

Since the dates were not formatted properly, I updated the formula to the following:

SUBSTITUTE(
    SUBSTITUTE(
      {Email Body} & "",
        "{Appraisal Contingency Date}",DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY") & ""),
        "{Conditional Approval Due Date}",DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY") & "")

It returns the following:
Appraisal Contingency Expires: Tue, 09/20/22
Conditional Loan Approval Due: Fri, 09/02/22

All seemed well, BUT, when there is a record that does not contain a date, the first formula still works but the second formula returns #ERROR!.

Any help would be greatly appreciated. :slightly_smiling_face:

1 Solution

Accepted Solutions

Works fine!

Could you try this instead?

SUBSTITUTE(
    SUBSTITUTE(
        {Email Body} & "",
        "{Appraisal Contingency Date}",
        IF(
            {Appraisal Contingency Date}, 
            DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY"),
            "No appraisal contingency date set"
        )
     ),
     "{Conditional Approval Due Date}",
     IF(
         {Conditional Approval Due Date},
         DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY"),
         "No conditional approval due date set"
    )
)

See Solution in Thread

6 Replies 6

Hi Jess, try this:

SUBSTITUTE(
    SUBSTITUTE(
        {Email Body} & "",
        "{Appraisal Contingency Date}",
        IF({Appraisal Contingency Date}, DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY") & "")
     ),
     "{Conditional Approval Due Date}",
     IF({Conditional Approval Due Date}, DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY") & "")
)

The formula’s trying to run DATETIME_FORMAT on a date that doesn’t exist, resulting in the error, and so the idea is to only run DATETIME_FORMAT if the date exists

This should work, but I haven’t tested it heh. Lemme know if you hit any issues

That makes sense! But alas, I am still getting an error. :frowning:

Ahh, sorry to hear that. Any chance you could put up screenshot of the relevant fields?

I actually created a base here that hopefully will be helpful. It’s my first time doing this so let me know if you have an issue accessing it!

Works fine!

Could you try this instead?

SUBSTITUTE(
    SUBSTITUTE(
        {Email Body} & "",
        "{Appraisal Contingency Date}",
        IF(
            {Appraisal Contingency Date}, 
            DATETIME_FORMAT({Appraisal Contingency Date},"ddd, MM/DD/YY"),
            "No appraisal contingency date set"
        )
     ),
     "{Conditional Approval Due Date}",
     IF(
         {Conditional Approval Due Date},
         DATETIME_FORMAT({Conditional Approval Due Date},"ddd, MM/DD/YY"),
         "No conditional approval due date set"
    )
)

BAM! You rock! Thank you so much! :smiling_face: