How to create dynamic rolling month and year?

Vivien Chong

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.

Heather Duff

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

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

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

@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

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

Thanks @Heather D , will test it out.