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.
Best Answer
-
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))
Answers
-
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()))
-
@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.
-
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))
-
@Hollie Green Thank you!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!