Trying to calc. tax of date & %

[viz]

I have a worksheet that calculates tax according to a quarterly tax scale -
i.e. each quarter the tax scale changes. If I have a column of dates and a
column of gross income, I would like to calculate a column of tax to pay
based on date and adjustable tax scale. I would then like to summarise the
total tax for the quarter - again based on dates. Have tried a few ways but
must be getting old

First Quarter (note our year starts 1st July)

Start Finish
1 Jul 2004 30 Sept 2004

Tax 13.12%

Date Income Tax to Pay
5 Jul 04 2500.00 ?
12 Jul 04 2620.00 ?
19 Jul 04 2650.00 ? Tax Summary 1st Quarter: ?

Etc


Second Quarter

Start Finish
1 Oct 2004 31st Dec 2004

Tax 14.00%

Date Income Tax to Pay
22 Nov 04 2725.00 ?
29 Nov 04 2650.00 ?
7 Dec 04 3200.00 ? Tax Summary 2nd Quarter: ?

Etc

To any that can help: TIA!

/viz

 

[Bob Phillips]

Here is one idea, assuming amount in B1

=B1*CHOOSE(INT((MONTH(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)))+2)/3),13.12%,14%,1
4.5%,15%)

 

[viz]

=B1*CHOOSE(INT((MONTH(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)))+2)/3),13.12%,14%,1
4.5%,15%)

Crikey. No wonder I could not work it out. Thanks!

/viz