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

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"

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.