# Roll Back Dates

Options
This discussion was created from comments split from: Rolling previous 12 month count that spans into previous year..

• ✭✭✭✭✭✭
Options

Could I ask for some help/guidance? I am trying to get my roll back dates to work and I'm having issues with the years showing correctly. I can't seem to figure out what I'm doing wrong/missing. It works today (using Today's date of 5/15/23) but if I change Today's date to 4/9/23, it doesn't.

I want to be able to go back three years (rollback) and it is the January/December months that I'm not getting to work properly I believe but everything I try doesn't work either.

I've attached screenshots

Formulas:

(line 27) - Current Month: =Today@row

(line 26 to 1) - 1 to 26 Month Back rows: =DATE(IF(MONTH(Date27) - 1 > 12, YEAR(Date27) - 1, YEAR(Date27)), IF(MONTH(Date27) - 1 > 12, MOD(MONTH(Date27) - 1, 12), MONTH(Date27) - 1), DAY(Date27)) - row changes as they move up

Appreciate any help.

Thanks, Peggy

• ✭✭✭✭✭✭
Options

Below is a formula that allows you to enter a number of months in one column and automatically calculate the appropriate date in another column. This works for both positive number of months and negative months and can accommodate going in either direction by multiple years.

=IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH([Original Date]@row) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH([Original Date]@row) - ABS([Number Of Months]@row) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS([Number Of Months]@row) < 1, MONTH([Original Date]@row) + (12 - ABS([Number Of Months]@row)), MONTH([Original Date]@row) - ABS([Number Of Months]@row)), 1))

• ✭✭✭✭✭✭
Options

Thank you @Paul Newcome - appreciate the help.

-Peggy

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!