Greater than, less than

[gahirtle]

Need a formula:

Column A: multiple dates, listed chronologically

Cell B1: A single date

I need a formula to count the number of dates in column A that fall within 1 year prior to the date in cell B1.

Thanks
George

[Aladin Akyurek]

On 2002-05-14 06:12, gahirtle wrote:
Need a formula:

Column A: multiple dates, listed chronologically

Cell B1: A single date

I need a formula to count the number of dates in column A that fall within 1 year prior to the date in cell B1.

Thanks
George

In B2 enter:

=EDATE(B1,-12)

Format B2 in a similar way to B1.

In B3 enter:

=SUMPRODUCT((A2:A100>=B2)*(A2:A100<=B1))

Note 1. The above is an inclusive between count. Adjust to suit.

Note 2. EDATE is available only when the Analysis Toolpak add-in is activated (via Tools|Add-Ins).

Aladin

[gahirtle]

What if I don't have access to the disk in order to load the analysis toolpak? Is there another formula?

[Jay Petrulis]

Hi,

If the Analysis ToolPak functions are unavailable, try

B2: =DATE(Year(B1)-1,Month(B1), Day(B1))

Bye,
Jay