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
Check out the Formula Handbook template!