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"
Answers
-
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
-
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 -
-
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
-
Test your formula first on a row where it should work without throwing an error. If it works, then add the IFERROR part.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!