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)), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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)), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!