Roll Back Dates

Options

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @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

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    Peggy Parchert ✭✭✭✭✭✭
    Options

    Thank you @Paul Newcome - appreciate the help.

    -Peggy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!