Hi Daisy,
Your reference to 'graph' threw me but if I ignore that and read the rest of
the question, it seems you want a table like this
1-Jan-06 2
1-Jul-06 3
1-Jan-07 3
.....
I will assume your data is in A1;F100 with row 1 holding labels
The major problem is the text "doesn't expire"
I selected the C column and used Edit | Replace to blank these cells out
(but see below)
In H1 I entered 1-1-2006 (that is the first item in my table)
In H2 I used the formula =DATE(YEAR(H1),MONTH(H1)+6,DAY(H1))
I copied this down the column
In I1 I entered the formula
=SUMPRODUCT(($B$2:$B$100<=H1)*($C$2:$C$100>=H1))+SUMPRODUCT(($B$2:$B$100<=H1)*($B$2:$B$100>0)*($C$2:$C$100=0))
and copied down the column
The second term allows for the blanks in column C
A better approach is to use Edit | Replace to convert "doesn't expire" to
1/1/2099 (or some other date in the far distant future)
Then the formula can be =SUMPRODUCT(($B$2:$B$100<=H1)*($C$2:$C$100>=H1))
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"daisy" <(E-Mail Removed)> wrote in message
news:90D2AAF0-2509-4283-9237-(E-Mail Removed)...
> Hi, can anyone please help?
>
> Is there a way to graph from a span of time. We have Members with certain
> benefits that is good forever basically and then Members by their Contract
> St
> and End Dt and Members in special programs. So the data looks like for
> example:
>
> Member StDt EndDt ContractFees Contract Type
> Member A 1/1/2006 12/31/2009 $500 Program C
> Member B 1/1/2006 doesn't expire $2000 Program A
> Member C 6/1/2006 5/1/2010 $1000 Program B
>
> So I'd like to be able to count the # of members over time from Jan 2006,
> then June 2006 ... April 2010
>
> So January 2006 would show 2 members and June 2006 would show 3 members
> April 2010 would show 2 members etc...
>
>
>
>
> --
> Thank you in advance!!!!
Similar Threads
