Lets say I have a table like

User StartDate EndDate Qty
John 5/1/07 5/10/07 10
Amy 5/14/07 5/20/07 5
Bob 5/25/07 6/10/07 17
Henry 6/1/07 6/5/07 4

I am trying to figure each user got per month, so I originally thought
it would be easy and I could use a pivot tablet to group the start
dates by month and then sum the qty. The problem is with people like
Bob is that the time frame spans two months, so I would want 7 counted
for May and 10 counted for June. I was trying to come up with a
formula to do this but I don't think that would work after trying a
few things since there isn't a way you could put the 7 in one cell for
may and the 10 in another cell for june. Any ideas on how to do this
with formulas, pivot table, or VBA?

Thanks,
Andrew V. Romero

In F2: =ROUND(D2*DAY(C2)/(C2-B+1),2)
E2: D2-E2

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Lets say I have a table like
>
> User StartDate EndDate Qty
> John 5/1/07 5/10/07 10
> Amy 5/14/07 5/20/07 5
> Bob 5/25/07 6/10/07 17
> Henry 6/1/07 6/5/07 4
>
> I am trying to figure each user got per month, so I originally thought
> it would be easy and I could use a pivot tablet to group the start
> dates by month and then sum the qty. The problem is with people like
> Bob is that the time frame spans two months, so I would want 7 counted
> for May and 10 counted for June. I was trying to come up with a
> formula to do this but I don't think that would work after trying a
> few things since there isn't a way you could put the 7 in one cell for
> may and the 10 in another cell for june. Any ideas on how to do this
> with formulas, pivot table, or VBA?
>
> Thanks,
> Andrew V. Romero
>