Within the last 12 months

I can't get my formula to pull violations reported only within the last 12 months. Can someone see what I am missing?

=COUNTIFS({Compliance Master File V2 EE-number}, [EE Number]@row, {Compliance Master File - Compliance Category}, "Reinstatement"), AND({Compliance Master File V2 Violation Date}<=[Today]@row, @{Compliance Master File V2 Violation Date}>= TODAY(-365)))

I have my historical count and I added a "today" column, if that is not needed - would love to remove it.

My working formula:

=COUNTIFS({Compliance Master File V2 EE-number}, [EE Number]@row, {Compliance Master File V2 - Compliance Category}, "Reinstatement")

Find more posts tagged with

Formulas

Accepted answers

jessica.smith

I simplified your cross-sheet ref names for demonstration, but does this get you the data you are looking for:

=COUNTIFS({Master_EE}, [EE Number]@row, {Master_Category}, "Reinstatement", {Master_ViolationDate}, >=TODAY(-365))

All comments

Paul Newcome

Try this instead:

=COUNTIFS({Compliance Master File V2 EE-number}, [EE Number]@row, {Compliance Master File - Compliance Category}, "Reinstatement", {Compliance Master File V2 Violation Date}, AND(@cell <= TODAY(), @cell >= TTODAY(-365)))

jessica.smith

I simplified your cross-sheet ref names for demonstration, but does this get you the data you are looking for:

=COUNTIFS({Master_EE}, [EE Number]@row, {Master_Category}, "Reinstatement", {Master_ViolationDate}, >=TODAY(-365))

Carmen Howard

That worked, thanks so much!!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.