Help with 6 month rolling summary metrics

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!

Find more posts tagged with

Accepted answers

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.