Determine qty charged per month
 

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

Determine qty charged per month
 

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)

wrote in message
ups.com...
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