Filter to capture all rows within the current month

I have a table called Marketing Calendar. It has columns for Event Name and Event Date.

I want to report to the marketing team both the Event Name and Event Date for the upcoming month. I also want one for the events within the current month.

Purpose is to create a newsletter to tell our membership what classes and presentations they can sign up for. But this background info is to alert the marketing team to show them what blurbs they should develop. I would rather not do this with automation after automation because I want marketing to be able to call up this information whenever they want, or display it on a dashboard.

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @wkhuser

One approach is to add two helper columns to your sheet. Both columns can be checkmark columns. You can name the columns whatever you like. Once you have the columns created, you can easily pull them into reports

The formula compares the Month of the Event Date to Today's date. The box will be checked when the statement is true

IsCurrentMonth

=IF(IFERROR(MONTH(Event Date),0)=MONTH(TODAY()),1)

IsNextMonth

=IF(IFERROR(MONTH(Event Date),0)=MONTH(TODAY())+1,1)

Will this work for you?

Kelly

wkhuser

It's unparseable as written here.

A. Should there be brackets around Event Date?

B. Should there be a parentheses between = and the second MONTH function? And therefore another parenthese at the end of the line?

C. Where do I put the @row? Would that be right next to the Event Date? [Event Date]@row?

D. Should the ,0 and ,1 parts be inside the parenthese for the MONTH function?

This worked: =IF((MONTH([Event Date]@row)) = (MONTH(TODAY())), 1, 0)

Thanks for pointing me to the month compared to today trick.

Kelly Moore

Hey @wkhuser

oops, yes on the brackets. The parenthesis you mentioned is part of the IFERROR in case you don't have a date in the Event date column.

=IF(IFERROR(MONTH([Event Date]@row), 0) = MONTH(TODAY()), 1)

Kelly

Paul Newcome

I would suggest a slight modification or two if your sheet could be rolling over into the next year or have multiple years on it.

Current month:

=IF(AND(IFERROR(MONTH([Event Date]@row), 0) = MONTH(TODAY()), IFERROR(YEAR([Event Date]@row), 0) = YEAR(TODAY())), 1)

Next month:

=IF(AND(IFERROR(MONTH([Event Date]@row), 0) = MONTH(TODAY()) - IF(MONTH(TODAY()) = 12, 11, 0), IFERROR(YEAR([Event Date]@row), 0) = YEAR(TODAY()) + IF(MONTH(TODAY()) = 12, 1, 0)), 1)

The current month just includes a year piece to only flag for this year if there are multiple years on your sheet. The next month includes that same piece, but it says that if the current month is December then we need to make "next month" January of next year instead of month #13.

wkhuser

Thank you both! I really appreciate the tutoring!

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.