Skip to main content

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. 🙂

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

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. 😦

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


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

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!

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"

    )

)

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:

Reply


Terms & Conditions