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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"(E-Mail Removed)" wrote:

> 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"(E-Mail Removed)" wrote:

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

> (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<>""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> (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<>""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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