COUNTIF for numbers of dates in column which are 30 days older than today

  • Thread starter Thread starter jonathan.glaser
  • Start date Start date
J

jonathan.glaser

I have a column with dates. I need a count of how many of these date
are older than today's date. I tried:

=COUNTIF(L7:L155,"-(=TODAY())>30")

and it returns 0

Anyone able to help here, please?

Thanks
Jonathan
 
Try: =COUNTIF(L7:L155,"<"&TODAY()-30)
to return the count of dates in the range which are 30 days older than today
(Above assumes there's no blank cells within the range)
 
Try: =COUNTIF(L7:L155,"<"&TODAY()-30)
to return the count of dates in the range which are 30 days older than today
(Above assumes there's no blank cells within the range)
 
(Above assumes there's no blank cells within the range)

If there's the possibility of blank cells within L7:L155, or formula cells
returning zero length null strings: "" within L7:L155, then perhaps it's
easier/better to use this alternative instead of the COUNTIF:
=SUMPRODUCT((L7:L155<TODAY()-30)*(L7:L155<>""))

---
 
(Above assumes there's no blank cells within the range)

If there's the possibility of blank cells within L7:L155, or formula cells
returning zero length null strings: "" within L7:L155, then perhaps it's
easier/better to use this alternative instead of the COUNTIF:
=SUMPRODUCT((L7:L155<TODAY()-30)*(L7:L155<>""))

---
 
I have a column with dates. I need a count of how many of these date
are older than today's date. I tried:

=COUNTIF(L7:L155,"-(=TODAY())>30")

and it returns 0

Anyone able to help here, please?

Thanks
Jonathan
Click to expand...
Try this, Column A has dates, Column B calculates the number of days ("d")
using the Dateif Function and Column C counts it if it is greater than 30
days. Note the brackets "{}" which are entered using Ctrl+Shift+Enter at the
end of the fourmula. Another variation of the formula in Column C that
works is {=COUNTIF(B:B,">30")}.
A B C
=DATEDIF(B1,NOW(),"d") {=COUNTIF(B1:B9,">30")}
1 7/18/2006 63 9
2 7/19/2006 62
3 7/20/2006 61
4 7/21/2006 60
5 7/22/2006 59
6 7/23/2006 58
7 7/24/2006 57
8 7/25/2006 56
9 7/26/2006 55
 
I have a column with dates. I need a count of how many of these date
are older than today's date. I tried:

=COUNTIF(L7:L155,"-(=TODAY())>30")

and it returns 0

Anyone able to help here, please?

Thanks
Jonathan
Click to expand...
Try this, Column A has dates, Column B calculates the number of days ("d")
using the Dateif Function and Column C counts it if it is greater than 30
days. Note the brackets "{}" which are entered using Ctrl+Shift+Enter at the
end of the fourmula. Another variation of the formula in Column C that
works is {=COUNTIF(B:B,">30")}.
A B C
=DATEDIF(B1,NOW(),"d") {=COUNTIF(B1:B9,">30")}
1 7/18/2006 63 9
2 7/19/2006 62
3 7/20/2006 61
4 7/21/2006 60
5 7/22/2006 59
6 7/23/2006 58
7 7/24/2006 57
8 7/25/2006 56
9 7/26/2006 55
 
Back
Top