Subtracting 1 month from TODAY, previous year error

=DATE(YEAR(day13), MONTH(day13) - 1, DAY(day13))

I am using the above formula to subtract one month from today's date for a rolling 12 month report. However, the formula errors above 15/01/22 (I expected it to revert to 15/12/21) is there a way I can build the formula so it does not error and continues into the previous year?



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Dayle_Taylor You'll need to add an IF statement in there to introduce a different formula when the month is January.

    =IF(MONTH(Day@row) = 1, DATE((YEAR(Day@row) - 1), 12, DAY(Day@row), DATE(YEAR(Day@row), MONTH(day@row) - 1, DAY(day13)))

    In English - If the Month is January, set the date using this year minus 1, month = 12, day = this day, otherwise, set the date using this year, this month minus 1, this day.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • @Jeff Reisman thanks for your prompt reply, yes I think an IF function is the way to do it, but as the bottom row of my table showing 15/09/22 is calculated using =TODAY() then the all date columns should change by +1 every day. Not sure if your formula would be compatible with that? I.e. The Day5 cell will change to February in 15 days.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!