SUBSTITUTE error when value doesn't exist

[Jess_Helmstette]

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:

[TheTimeSavingCo]

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

[Jess_Helmstette]

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

[TheTimeSavingCo]

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

[Jess_Helmstette]

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!

[TheTimeSavingCo · Accepted solution]

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

[Jess_Helmstette]

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