IFERROR formula

BonW

Happy New Year! I need help with the formula below, I am getting #UNPARSEABLE. I am wanting to add 10 years to Date Completed if the Freq="10 Years", add 6 months if the Freq="Semi-Annual", and add 1 year if the Freq="Annual".

=IF([Inspection Freq]@row = "", "", IFERROR(IF([Freq]@row = "Semi-Annual", IFERROR(DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 6, DAY([Date Completed]@row)), DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row) - 6, DAY([Date Completed]@row))))), IF([Freq]@row = "10 Years", IFERROR(DATE(YEAR([Date Completed]@row) + 10, MONTH([Date Completed]@row), DAY([Date Completed]@row)))), DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row), DAY([Date Completed]@row)), ""))

Thank you.

jmyzk_cloudsmart_jp

Hi @BonW

Here is my demo solution.

https://app.smartsheet.com/b/publish?EQBCT=ef3a1eefe9bb4333813f3e42c67980dc

Formula

=IF(ISDATE([Date Completed]@row), 
   IF(Freq@row = "", "", 
      IF(Freq@row = "10 Years", DATE(YEAR([Date Completed]@row) + 10, MONTH([Date Completed]@row), DAY([Date Completed]@row)), 
      IF(Freq@row = "Semi-Annual", DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 6, DAY([Date Completed]@row)), 
      IF(Freq@row = "Annual", DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row), DAY([Date Completed]@row)), 
      ""))))
, ""

The formula checks first if the Date Completed column contains a valid date using ISDATE. If it does, it proceeds to evaluate the Freq column. If Freq is blank, it returns a blank. If Freq equals "10 Years," it adds 10 years to the Date Completed. If Freq equals "Semi-Annual," it adds 6 months to the Date Completed. If Freq equals "Annual," it adds 1 year to the Date Completed. If none of these conditions are met, it returns a blank.

Here are my recommendations.

• Avoid using IFERROR where possible. Instead, use explicit IF conditions to handle exceptions. IFERROR is a catch-all that might lead to unexpected results.
• Removed unnecessary nesting, simplifying the formula for better readability and easier maintenance.

jmyzk_cloudsmart_jp

Great!

jmyzk_cloudsmart_jp

Hi @BonW

Here is my demo solution.

https://app.smartsheet.com/b/publish?EQBCT=ef3a1eefe9bb4333813f3e42c67980dc

Formula

=IF(ISDATE([Date Completed]@row), 
   IF(Freq@row = "", "", 
      IF(Freq@row = "10 Years", DATE(YEAR([Date Completed]@row) + 10, MONTH([Date Completed]@row), DAY([Date Completed]@row)), 
      IF(Freq@row = "Semi-Annual", DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 6, DAY([Date Completed]@row)), 
      IF(Freq@row = "Annual", DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row), DAY([Date Completed]@row)), 
      ""))))
, ""

The formula checks first if the Date Completed column contains a valid date using ISDATE. If it does, it proceeds to evaluate the Freq column. If Freq is blank, it returns a blank. If Freq equals "10 Years," it adds 10 years to the Date Completed. If Freq equals "Semi-Annual," it adds 6 months to the Date Completed. If Freq equals "Annual," it adds 1 year to the Date Completed. If none of these conditions are met, it returns a blank.

Here are my recommendations.

• Avoid using IFERROR where possible. Instead, use explicit IF conditions to handle exceptions. IFERROR is a catch-all that might lead to unexpected results.
• Removed unnecessary nesting, simplifying the formula for better readability and easier maintenance.

BonW

Hi Jmyzk_Cloudsmart_jp,

Thank you so much it works perfectly! and thank you for the advise of avoiding use IFERROR.

jmyzk_cloudsmart_jp

Great!