Roll Back Dates

System

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

Peggy Parchert

@Paul Newcome

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

Date - 3162023.png

date - 5152023.png

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

Paul Newcome

@Peggy Parchert

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))

Peggy Parchert

Thank you @Paul Newcome - appreciate the help.

-Peggy

Paul Newcome

Happy to help. 👍️