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

PKane

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.

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.

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.

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. 👍️