Countif date is in the next 30 days

Can a COUNTIF (or other formula) count if a date is in the next 30 days or is in the past compared to the inspection date? I have conditional formatting flagging this, but would also like to count how many date violations there are as I will eventually add more expiration date columns.

I have only been able to figure out how to count if the expiration date is in the past.

I have already put in feedback asking if a formula or column could count the number of cells that have been changed / flagged by conditional formatting. Not sure if that would work with all situations, but would be nice for this instance.

Find more posts tagged with

Formulas

Conditional Formatting

Accepted answers

Hollie Green

Sorry about that I missed that it was in reference to the Inspection Date the below will work in reference to the inspection date.

For each row

=COUNTIFS([Fire Extinguisher Expiration Date]@row,<=([Inspection Date]@row + 30),[Fire Extinguisher Expiration Date]@row,>=[Inspection Date]@row)

For total count

=COUNTIFS([Fire Extinguisher Expiration Date]:[Fire Extinguisher Expiration Date], AND(@cell <= ([Inspection Date]@row + 30), @cell >= [Inspection Date]@row))

All comments

Hollie Green

This formula will give a count of 1 on the row it occurs on

=COUNTIFS([Fire Extinguisher Expiration Date]@row,<=Today(30),[Fire Extinguisher Expiration Date]@row,>=TODAY())

If you want to count all of the Fire Extinguisher Expiration Dates that occur on this sheet within the next 30 days you would use

=COUNTIF([Fire Extinguisher Expiration Date]:[Fire Extinguisher Expiration Date], AND(@cell <= TODAY(30), @cell >= TODAY()))

Jade Boring

@Hollie Green Thank you! That works, but wouldn't all dates eventually be flagged in that case? Once TODAY reaches <30 days? Trying to get an idea of how many violations there are at the time of the inspection.

Hollie Green

Sorry about that I missed that it was in reference to the Inspection Date the below will work in reference to the inspection date.

For each row

=COUNTIFS([Fire Extinguisher Expiration Date]@row,<=([Inspection Date]@row + 30),[Fire Extinguisher Expiration Date]@row,>=[Inspection Date]@row)

For total count

=COUNTIFS([Fire Extinguisher Expiration Date]:[Fire Extinguisher Expiration Date], AND(@cell <= ([Inspection Date]@row + 30), @cell >= [Inspection Date]@row))

Jade Boring

@Hollie Green Thank you!!!

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.