please suggest a formula

[User]

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....

 

[Roger Govier]

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

 

[Mike H]

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.