help with dates & calculations

[Nikhil]

I receive payments from different people every month... every month, there
are additions to the list as also people who drop out from the system.... I
want to calculate monthwise what my income is...e.g

Start Date---Payment----Termination Date Apr'08 May'08
Jun'08
12-Apr-08 5000 =(5000/30)*19
5000 5000
1-Apr-08 10000 15-May-08 10000
5000 -----
5-Apr-08 5000 25-Apr-08 =(5000/30)*21 ----
----
1-Jun-08 4000 ------
---- 4000


can some one help....the forumla becomes quite complex....and am not getting
the hang, where i am going wrong

Nikhil

 

[Spiky]

I receive payments from different people every month... every month, there
are additions to the list as also people who drop out from the system.... I
want to calculate monthwise what my income is...e.g

Start Date---Payment----Termination Date Apr'08 May'08
Jun'08
12-Apr-08 5000 =(5000/30)*19
5000 5000
1-Apr-08 10000 15-May-08 10000
5000 -----
5-Apr-08 5000 25-Apr-08 =(5000/30)*21 ----
----
1-Jun-08 4000 ------
---- 4000

can some one help....the forumla becomes quite complex....and am not getting
the hang, where i am going wrong

Nikhil

I don't understand quite everything. Is your monthly income already in
the "Apr'08", "May'08", etc columns? A SUM at the bottom of the column
would be the easiest way to get the monthly total.

Or are you trying to find a better formula for the "=(5000/30)*19" in
those columns?

 

[Nikhil]

I have the Start Date, Payment & Termination date (where terminated)....I
want the payments to be reflected for each month....

 

[Spiky]

I have the Start Date, Payment & Termination date (where terminated)....I
want the payments to be reflected for each month....

I think I got it, with normal functions that pretty much every version
of Excel has available. It is a little complex to make one formula
that can do everything. This SHOULD calculate the monthly amount
regardless of the start and term dates and which month you are looking
at.

I've assumed that your column titles are in Row 1 and Start Date,
Payment, and Term Date are in Columns A, B, C respectively. So this
could go in D2 and be copied to all other cells needed, across or
down. I've also assumed that your column titles for the months must be
the last date of the month, and they cannot be entered or formatted as
text. So 5/31/2008, 6/30/2008, etc. You can still format those cells
to show the month however you want, like your "May'08" above. If you
don't put the last date of the month in these cells, this formula gets
worse.

=IF($A2>$C2,"Date Error",IF($A2>D$1,"",IF($C2<D$1-(DAY(D
$1)+1),"",IF(AND(MONTH($A2)=MONTH(D$1),MONTH($C2)=MONTH(D$1)),$B2*($C2-
$A2+1)/DAY(D$1),IF($A2<D$1-DAY(D$1)+1,MIN($B2,$B2*MIN(DAY(D$1),$C2-(D
$1-DAY(D$1)))/DAY(D$1)),$B2*((D$1-$A2+1)/DAY(D$1)))))))