Counting the number of items between two dates

[ihmunro]

Morning

I have been working with SUMPRODUCT which works well and I am not sure if this is what I need to use.

I am trying to identify the number of line items that have dates between today and 30 days, 30 - 90 days and > 90 days.

I am trying to do as a lookup.

Many thanks in advance.

Iain

 

[Damon Ostrander]

Hi Iain,

I'm not sure whether you are referring to dates 30-90 days in the future, or the past. Assuming the future, you could use the following to obtain your counts:

In an adjacent column use the formula

=IF(AND($B4>TODAY(),$B4<TODAY()+30),1,0)

to yield a 1 if the data is within 30 days of today, 0 otherwise. Then sum the column to get the number of days.

Similarly you can use formulas:

=IF(AND($B4>TODAY()+30,$B4<TODAY()+90),1,0)

and

=IF($B4>TODAY()+90,1,0)

to get counts of the dates 30-90 days in the future, and more than 90 days in the future, respectively.

 

[Damon Ostrander]

Hi again Ian,

For some reason I didn't get the complete formulas copied in my previous post for the first two formulas. They should have been:

=IF(AND($B4>TODAY(),$B4<TODAY()+30),1,0)

and

=IF(AND($B4>TODAY()+30,$B4<TODAY()+90),1,0)

Damon

 

[IML]

Damon's post with HTML diabled

On 2002-11-05 14:21, Damon Ostrander wrote:
Hi again Ian,

For some reason I didn't get the complete formulas copied in my previous post for the first two formulas. They should have been:

=IF(AND($B4>TODAY(),$B4<TODAY()+30),1,0)

and

=IF(AND($B4>TODAY()+30,$B4<TODAY()+90),1,0)

Damon

 

[Damon Ostrander]

Thanks IML,

I thought I had pasted the wrong string. I'll try to remember to disable HTML after this.

Damon