Determine qty charged per month

[rrstudio2]

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

 

[Bob Phillips]

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)