Excel question: COUNTIF to count cells containing expired dates

November 27, 2019 11:02 AM

I would like to be able to count the number of cells in an array that contain a date more than 3 years old. I would like it to be dynamic so that the expiration date changes in relation to the current date. The following does not work: =COUNTIF(Table1[Date],"<TODAY()-1095") Could someone point me in the right direction?
posted by jwhite1979 to Computers & Internet (5 answers total)

Best answer: Greater than / less than / equality operators have a weird syntax in COUNTIF... they need to be in quote marks and then concatenated with the rest of the logical test like this:

COUNTIF(E7:E11, "<"&TODAY()-1095)
posted by protorp at 11:09 AM on November 27, 2019 [8 favorites]

If you care about it being exactly 3 years, then =COUNTIF(Table1[Date],"<"&EDATE(TODAY(),-36)) gets you the date 36 months before today, and works with leap years, etc,...
posted by ambrosen at 12:50 PM on November 27, 2019 [2 favorites]

I was going to hop in with a solution involving a countif of -1 * (Logical Test), but protorp's is so much less horrible that I'll keep my toxic waste to myself
posted by scruss at 12:52 PM on November 27, 2019 [3 favorites]

this exact problem has been a bugbear of mine for some time - thank you, protorp!
posted by jb at 2:22 PM on November 27, 2019

Response by poster: Thank you guys so much. I was pulling my hair out for hours.
posted by jwhite1979 at 4:20 AM on December 2, 2019