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

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

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

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.

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

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

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

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

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

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