Help with 6 month rolling summary metrics

Rob Laguerre

I have a date column in a sheet and I am looking to count the number of dates that fall within the current, then current month +1, current month +2, so on a so forth. I think the tricky part is making sure the counts are accurate based on year as well. So since today is October 2, 2021 the rolling 6 months would be as follows:

October 2021 - Current Month

November 2021 - Current Month +1

December 2021 - Current Month +2

January 2022 - Current Month + 3

February 2022 - Current Month +4

So on and so forth...

Any help is greatly appreciated!

Paul Newcome

Try something like this...

Current Month:

=COUNTIFS([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Month + 1:

=COUNTIFS([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1)))))

Continuing:

Change the bold portion above to +2 and -10, +3 and -9, so on and so forth. The ABS of the two numbers should equal 12.

Rob Laguerre

Thanks, @Paul Newcome ! I figured out another approach that got me exactly what I needed using some helper columns. I appreciate the reply.

Paul Newcome

Happy to help. 👍️