Cdn Gas Guy,

OK, sorry about that.

Insert two rows above row 1, so that you have your D1 date in D3.

Then in D1, enter
=DATE(YEAR(D$3),MONTH(D$3),1)
and in D2, enter
=DATE(YEAR(D$3),MONTH(D$3)+1,0)

Then in D4, enter
=IF(D$1$B4,0,IF(D$2<$A4,0,IF(AND(D$2<$B4,D$1$A4) ,DAY(D$2),IF(AND($A4=D$1,$B4<=D$2),$B4-$A4+1,IF(AND($B4=D$1,$B4<=D$2),DAY($B4),IF(AND($A 4=D$1,$A4<=D$2),(DAY(D$2)-DAY($A4)+1)))))))*$C4/DAY(D$2)

(I changed to cell references because the formula got too long and was
erroring out.)

Then copy D1:D2 to the right, and D4 over and down as far as is needed.

I think I have the IF clauses in the correct order...

HTH,
Bernie
MS Excel MVP



"Cdn Gas Guy" wrote in message
...
The solution Bernie noted correctly assumes what I obviously failed to
explain earlier. The only problem with Bernies solution is that a start
and
end date within a single month provides an incorrect answer.

Bernies Correct Assumptions we
A1:C1 have labels, cells D1, E1, and so on to the right have dates like
11/1/2007, 12/1/2007, etc.

Cell A2 has the start date for that row (and other start dates are below)
Cell B2 has the end date for that row (and other end dates are below)
Cell C2 has the daily units for that row (and other daily units are below)

Now if I had cells A2 and B2 contain Nov 15/07 and Nov 17/07 (3 days
appart)
the result should be 3 units. Bernies formula gives 17 units.


"Rick Rothstein (MVP - VB)" wrote:

I'm not 100% clear on your layout. Perhaps you could show the relevant
column letter designations with their heading text. Specifically, where
are
your Start and End dates listed at?

Rick

"Cdn Gas Guy" <Cdn Gas wrote in message
...
I have a list of contracts with daily volumes and dates which start and
end
at various points over months/years organized in rows. Next to the this
I
have columns for every calendar month moving years into the future.
What I
need to do is have a formula to return the average contract volume
applicable
to every given calendar month across the months.

ie. If the contract was for 30 units per day and the contract date
started
Nov 30/07 then the average daily volume for Nov/07 would be 1 unit.

Any help will be greatly appreciated.