Returning data from within a date range

[Cdn Gas Guy]

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.

 

[JLGWhiz]

In my math class your example would equate to 30 units per day average. So
you need to clarify how you got to an average of 1.

 

[Cdn Gas Guy]

30 units per day for one day = 30 units. Divided by 30 days in November =
average 1 unit per day

 

[JLGWhiz]

30 units per day x 30 days = 900/30 = 30 units per day.
30 units per month @ 30 days in mo/30 = 1 unit per day.

Ending date per contract - Starting Date per contract = total time in days
Sum(Delivery by Contract)/total time in days = average units per day

The terminology needs to match the expected outcome or you will get some
erroneous answers.

 

[Bernie Deitrick]

Cdn Fas Guy,

My Assumptions:
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)

In cell D2, enter the formula (this is a long one, so remove the line breaks):

=IF(DATE(YEAR(D$1),MONTH(D$1),1)>$B2,0,IF(DATE(YEAR(D$1),MONTH(D$1)+1,0)<$A2,0,IF(AND(DATE(YEAR(D$1),MONTH(D$1)+1,0)<$B2,DATE(YEAR(D$1),MONTH(D$1),1)>$A2),$C2*DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0)),IF(AND($B2>=DATE(YEAR(D$1),MONTH(D$1),1),$B2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),DAY($B2)*$C2,IF(AND($A2>=DATE(YEAR(D$1),MONTH(D$1),1),$A2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),(DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0))-DAY($A2)+1)*$C2)))))/DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0))

Then copy this to the right and down as far as you need.

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Just to jump into this thread, I think JLGWhiz's comment centered around
this.... your original message said your contract started November 30th
which means your contract only had one day in it before the month ended...
30 units per day divided by the one day in the month your contract was
active for equals 30 units per day average.

Rick

 

[Rick Rothstein \(MVP - VB\)]

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]

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 were:
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.

 

[Bernie Deitrick]

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($A4>=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 D12 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]

Works great, thanks Bernie.

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($A4>=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 D12 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

 

[Bernie Deitrick]

Whew! Thanks for letting me know ;-)

Bernie