Subtracting 1 month from TODAY, previous year error

Dayle_Taylor

=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

@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.

Dayle_Taylor

@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.