Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine if a month falls between two dates. | Excel Worksheet Functions | |||
Need a way to determine the # of Saturdays in a month | Excel Worksheet Functions | |||
How to determine the number of month between 2 periods? | Excel Discussion (Misc queries) | |||
How to determine the number of month between 2 periods? | Excel Worksheet Functions | |||
Determine Last working day of Month | Excel Programming |