Getting a #Invalid Data Type error when info is not present in a referenced date column

I was able to get my formula to output a modified date that is pushed out by a couple of years with the following formula, but it keeps showing #Invalid Data Type for any cells that aren't populated with a date in the referenced.

This is the formula I am using: =DATE(YEAR([Install Date]@row) + 5, MONTH([Install Date]@row), DAY([Install Date]@row))

I am a beginner - so any help in a simple form would be very appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328436#Comment_328436

@PKane What Paul said 🔝 will work fine. I use that method a lot.

An alternative -

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

This tells it to only try the formula if the Install Date field on that row is a date value. If it's not a date value, leave the cell blank.

The difference is that the IFERROR method suppresses the error messages, while the IF ISDATE method prevents the errors.

Since you are new to this, check out the links in my signatures. These are very helpful resources. If you haven't already, in the Smartsheet solution center you can search for "formula examples" to find a template you can add to your Smartsheet. This template sheet contains helpful examples of most functions.

All comments

Paul Newcome

Try this:

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328436#Comment_328436

@PKane What Paul said 🔝 will work fine. I use that method a lot.

An alternative -

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

This tells it to only try the formula if the Install Date field on that row is a date value. If it's not a date value, leave the cell blank.

The difference is that the IFERROR method suppresses the error messages, while the IF ISDATE method prevents the errors.

PKane

Thank you - Jeff and Paul. I really appreciate your knowledge and willingness to share it!

Both solves work great and I learned some new stuff - much appreciated! :)

Paul Newcome

Happy to help. 👍️

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.