Countif date is in the next 30 days

Jade Boring

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.

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))

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!!!