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.
Best Answers
-
Try this:
=IFERROR(DATE(YEAR([Install Date]@row) + 5, MONTH([Install Date]@row), DAY([Install Date]@row)), "")
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Try this:
=IFERROR(DATE(YEAR([Install Date]@row) + 5, MONTH([Install Date]@row), DAY([Install Date]@row)), "")
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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! :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
