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

[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

 

[Guest]

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)

 

[Guest]

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)

 

[Guest]

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

---

 

[Guest]

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

---

 

[PBalmanno]

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

 

[PBalmanno]

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