Formula Error - trying to subtract 1 day.

pamwats

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?

Michael Culley

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.

Michael Culley

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.

Michael Culley

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.

pamwats

That worked. Simpler formula is the better option :)