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

Hi Jonathan,

To count the number of dates in a column that are 30 days older than today, you can use the COUNTIFS function in Excel. Here's how you can do it:

1. Select a cell where you want to display the result.
2. Type the following formula:
   Code:

   =COUNTIFS(L7:L155,"<"&TODAY()-30)

3. Press Enter to get the result.

This formula uses the COUNTIFS function to count the number of cells in the range L7:L155 that meet the following criteria:

- The cell value is less than today's date minus 30 days (i.e., the date is 30 days older than today).

The "<" operator is used to compare the cell value with the date calculated by the TODAY() function minus 30 days. The "&" operator is used to concatenate the "<" operator with the result of the TODAY() function minus 30 days.

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

wrote in message
ups.com...
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