No problem can be solved from the same level of consciousness that created it.
Albert Einstein-

Thank you so much David!! I made a small modification, and went with this:
=IF(AND($A5E$2,$B5E$2),0,($D5/$C5)*(MIN($B5,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4))

Regards,
Ryan---



--
RyGuy


"David Biddulph" wrote:

You might try
=($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4)

Note that you've added 1 in your C5 formula (so effectively counted start
and end dates) but in your E5 formula you haven't added an extra day, so you
need to add an extra day at the end.
--
David Biddulph

"ryguy7272" wrote in message
...
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