Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with dates & calculations
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with dates & calculations
On Jun 18, 6:50 am, Nikhil wrote:
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with dates & calculations
I have the Start Date, Payment & Termination date (where terminated)....I
want the payments to be reflected for each month.... "Spiky" wrote: On Jun 18, 6:50 am, Nikhil wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with dates & calculations
On Jun 18, 11:29 pm, Nikhil wrote:
I have the Start Date, Payment & Termination date (where terminated)....I want the payments to be reflected for each month.... "Spiky" wrote: On Jun 18, 6:50 am, Nikhil wrote: 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? 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($A2D$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))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations using dates -- HELP! | Excel Discussion (Misc queries) | |||
Removing Weekend dates from Day Calculations | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Difference in dates calculations except between certain times. | Excel Discussion (Misc queries) | |||
auto-calculations using calendar and dates (Excel-Office 2000) | Excel Worksheet Functions |