Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
please suggest a formula
i have 2 dates i.e,
eg: Issue Dt & Expiry Dt. Amount 06.01.10 31.12.10 10000000/- now i would like to calculate the commission for every quarter from the issue dt. till expiry dt.i.e, from 06.01.10 next chargeable commission is in the month of April like that i have to calculate till the expiry dt. (Amount*.6%)/4+Service tax so please suggest me some formula wherein if i provide the issue dt. & Expiry dt. it should give me the commission amount along with the month automatically till expiry date Eg:- 06/04/10 'X' amount 06/07/10 'X' amount till 31/12/10.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
please suggest a formula
Hi
In cell D1 enter 06/04/2010 in E1 enter =DATE(YEAR(D1),MONTH(D1)+3,DAY(D1)) and copy across through F1:N1 Ensure that you have valid Excel dates in A2 and B2 e.g. 06/01/2010, 31/12/2010 then in cell C2 enter =IF(AND(D$1=$A2,D$1<=$B2),($C2/4*6%)*(1+salestax),0) and copy across and down Salestax represent your sales tax figure as a % value -- Regards Roger Govier User wrote: i have 2 dates i.e, eg: Issue Dt & Expiry Dt. Amount 06.01.10 31.12.10 10000000/- now i would like to calculate the commission for every quarter from the issue dt. till expiry dt.i.e, from 06.01.10 next chargeable commission is in the month of April like that i have to calculate till the expiry dt. (Amount*.6%)/4+Service tax so please suggest me some formula wherein if i provide the issue dt. & Expiry dt. it should give me the commission amount along with the month automatically till expiry date Eg:- 06/04/10 'X' amount 06/07/10 'X' amount till 31/12/10.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
please suggest a formula
Hi,
I don't fully understand this question but here's a start. To get your dates put this in a cell and drag right =MIN(DATE(YEAR($A$2), MONTH($A$2)+COLUMN(A1)*3, DAY($A$2)),$B$2) You will now get the 4 dates ending with 31/12/2010 Not this is where i'm confused because nothing seems to be happening to the original sum so isn't the formula the same for each month =($C$2*0.6%)/4+servicetax Where servicetax is a named range containg the tax rate -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "User" wrote: i have 2 dates i.e, eg: Issue Dt & Expiry Dt. Amount 06.01.10 31.12.10 10000000/- now i would like to calculate the commission for every quarter from the issue dt. till expiry dt.i.e, from 06.01.10 next chargeable commission is in the month of April like that i have to calculate till the expiry dt. (Amount*.6%)/4+Service tax so please suggest me some formula wherein if i provide the issue dt. & Expiry dt. it should give me the commission amount along with the month automatically till expiry date Eg:- 06/04/10 'X' amount 06/07/10 'X' amount till 31/12/10.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Suggest Read only | Excel Discussion (Misc queries) | |||
What would you suggest | Excel Discussion (Misc queries) | |||
Can any one suggest code/formula for finding the CAGR | Excel Worksheet Functions | |||
use excel formula to suggest cell value, but be able to override | Excel Discussion (Misc queries) | |||
How to suggest a new feature? | Excel Worksheet Functions |