Adding 5 years to a date. How do I avoid getting #invalid data when the source date is blank?

PKane
PKane ✭✭✭✭
edited 06/22/23 in Formulas and Functions

Hello Smartsheet Community,

I am trying to have a formula add 5 years to a date in a column, but when the source date column is blank/has not date in it - it returns #INVALID DATA TYPE.

I would like the formula to look at the latest install date and add 5 years to it, and if its blank return nothing.

How can I adjust my formula to return nothing if blank?

Here is the formula I'm using:

=DATE(YEAR([Latest Install Date]@row) + 5, MONTH([Latest Install Date]@row), DAY([Latest Install Date]@row))

Here is a snip from the sheet"


Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 06/22/23

    Hi @PKane

    Wrap your if statement in an IFERROR formula.

    =IFERROR(IF([DC UPS Completed (Date)]@row) = “”, “”, DATE(YEAR([Latest Install Date]@row) + 5, MONTH([Latest Install Date]@row), DAY([Latest Install Date]@row)), "")

    Then it will add a blank string of text when there's an error.

    You can replace the final "" with some other text if you desire.

    All the best,

    -Ray

  • PKane
    PKane ✭✭✭✭

    Hello @Ray Lindstrom thanks so much for your help with this issue -

    I tried the formula you'd supplied (and I corrected a column name that was wrong) and its reporting back as #UNPARSEABLE

    Here is the formula in its current state:

    =IFERROR(IF([Latest Install Date]@row) = “”, “”, DATE(YEAR([Latest Install Date]@row) + 5, MONTH([Latest Install Date]@row), DAY([Latest Install Date]@row)), "")

    Seems really close -

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    You need to wrap your formula in the IFERROR formula's value section.

    If your formula works: =IF([Latest Install Date]@row) = “”, “”, DATE(YEAR([Latest Install Date]@row) + 5, MONTH([Latest Install Date]@row), DAY([Latest Install Date]@row))

    Then wrap it in the IFERROR

    =IFERROR(value, value_if_error)

    Value will be your formula

    Value_if_error will be your blank string, or some other text you define.

    =IFERROR(IF([Latest Install Date]@row) = “”, “”, DATE(YEAR([Latest Install Date]@row) + 5, MONTH([Latest Install Date]@row), DAY([Latest Install Date]@row)), "")

    Here's where you can see the details: https://help.smartsheet.com/function/iferror

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 06/22/23

    Test your formula first on a row where it should work without throwing an error. If it works, then add the IFERROR part.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!