E2:AB2 contains dates, with the end of the month in each column.
For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009

S4:AB4 contains the number of days in each month.
E4 = 31, F4 = 29...AB4 = 31

A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000
A5 = 1/29/08
B5 = 1/15/09
C5 = 353 (1/15/09 - 1/29/08) + 1
D5 = $120,000
E5 = $680
The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to
1/31/08 is 2. This is multiplied by the total amount of revenue collected
divided by the total number of days over which the revenue is collected
(120,000/353).

I think I am close to a solution, but I can't quite resolve this thing.







--
RyGuy


"Niek Otten" wrote:

<Based on the same logic, I would expect to see $9,858 in F5.

That bit I don't understand. What is the logic?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ryguy7272" wrote in message ...
| Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2.
| Using the day function, I have the number of days in that month, which is 31,
| in cell E4. I have a start date (1/29/2008) in A5 and an End Date
| (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000)
| in D5. I am trying to allocate that $120,000 over the entire time interval,
| from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because
| there are 353 days between the start date and the end date and there are two
| days between 1/29/2008 and 1/31/2008. Based on the same logic, I would
| expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I
| would expect to see $5,439 in Q5. Then, when I sum all the data elements on
| this row, I would like to see a total of $120,000, which indicates that all
| payments have been made and accounted for. After all this, I would go to E5,
| and fill-down a few rows, and all cells should populate with the correct
| payments/amounts.
|
| I am using this function:
| =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5))))
|
| It is close, but not quite correct. I'd appreciate any and all assistance.
| This is somewhat complicated for me, only because I can't seem to get my mind
| into it. In all honesty, I don't think it is all that difficult though.
|
| Thanks!!
|
|
| --
| RyGuy