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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/20/23 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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/19/23

    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
    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
    Hollie Green ✭✭✭✭✭✭
    edited 06/20/23 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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!