Formula Error - trying to subtract 1 day.

Options

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

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    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
    Michael Culley ✭✭✭✭✭
    edited 08/17/22 Answer ✓
    Options

    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
    pamwats
    edited 08/17/22
    Options

    That worked. Simpler formula is the better option :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!