How to create dynamic rolling month and year?

Options

I am trying to create a table to calculate score from another sheet on a 12 months rolling basis, this data is to be published on the dashboard.

It's easy to have the 12th month data which is current month.

However this formula doesn't work across the board as shown above, month-9 would be 0.

How can I have a formula to capture the rolling month and year so that I can then sum or average the score from the score sheet base on these rolling month and year?

Appreciate assistance. Thanks.

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Vivien,

    I think I may have come up with something. See screenshot below.


    I added a helper row (row 1) that started with the number 1 on M-1 and ended on M-11 on 11, then entered 0 on M-12. This is assuming you will always want M-12 to list the current month.

    Here is the formula I put in row 2:

    =IF((MONTH(TODAY()) + [M-1]1) < 13, MONTH(TODAY()) + [M-1]1, MONTH(TODAY()) + [M-1]1 - 12)

    And the formula I put in row 3:

    =IF((OR((MONTH(TODAY()) + [M-1]1) > 12, [M-1]1 = 0)), YEAR(TODAY()), YEAR(TODAY()) - 1)


    Let me know if this works for you!


    Best,

    Heather

Answers

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    Additional question, since I am using TODAY function in this metric sheet to cross reference the source sheet, will it be updated automatically without opening this metric sheet?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Hi @Vivien Chong ,


    The TODAY function updates automatically, so if you are using the metrics in a dashboard, it will update automatically. A few follow-up questions:

    -Am I right in understanding that your year begins in April and ends in March?

    -When M-12 is over and M-1 begins, do you want the M-2 through M-12 columns to be blank, or do you want them to still show the previous year's data?


    Thanks,

    Heather

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    @Heather D the formula should be auto calculating the constant rolling.

    If this month is March, it should be showing: April to March

    If this month is April, it should be showing: May to April


    With these constant rolling dynamic month and year, I can then capture my data base on 12 months rolling constantly.

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Vivien,

    I think I may have come up with something. See screenshot below.


    I added a helper row (row 1) that started with the number 1 on M-1 and ended on M-11 on 11, then entered 0 on M-12. This is assuming you will always want M-12 to list the current month.

    Here is the formula I put in row 2:

    =IF((MONTH(TODAY()) + [M-1]1) < 13, MONTH(TODAY()) + [M-1]1, MONTH(TODAY()) + [M-1]1 - 12)

    And the formula I put in row 3:

    =IF((OR((MONTH(TODAY()) + [M-1]1) > 12, [M-1]1 = 0)), YEAR(TODAY()), YEAR(TODAY()) - 1)


    Let me know if this works for you!


    Best,

    Heather

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    Thanks @Heather D , will test it out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!