Formula Error  trying to subtract 1 day.
I'm trying to calculate an expiration date and have the resulting number be X years later, but one day prior to the original date. The formula is: =DATE(YEAR([CV Date Signed]@row) + 2, MONTH([CV Date Signed]@row), DAY([CV Date Signed]@row)  1)
This worked fine on Row 1, where the CV Date Signed date was 2/15/22. But on row 2, where the date was 05/01/22, I got an INVALID VALUE error. I took out the "1" at the end, and it worked fine.
How do I write the formula so that Smartsheet knows to 1 to April 30th instead of May 0?
Best Answer

Also not sure how precise you need to be but couldn't you also just do =[cv date signed]@row + 729 ? Which is 2 years minus 1 day.
Answers

Not sure how to account for some months that have 31 days but here's an idea of what you could do.
=DATE(YEAR([cv date signed]@row) + 2, MONTH([cv date signed]@row), IF(DAY([cv date signed]@row)  1 = 0, 30, DAY([cv date signed]@row)))
I suppose you could also do an if statement checking if month is Jan, Mar, May etc and if Day 1 =0, do 31 for those months.

Also not sure how precise you need to be but couldn't you also just do =[cv date signed]@row + 729 ? Which is 2 years minus 1 day.

That worked. Simpler formula is the better option :)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!