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

PKane

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"

Ray Lindstrom

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

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

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

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