TAX Calculator Date Query...Please Help

[Mark McInerney]

Hi,

I am trying to build a tax calculator/forecasting tool. I need it to be relevant and up to date.

In summary Cell A1 is a date entry box. This is always my starting point.

There are a number of transactions that are linked to a quarterly and bi annual cycle and I cannot come up with a formula - driving me mad!

A typical example is: The VAT is due on the 23rd of every second month, starting in Jan and going thru Mar, May, Jul, Sep, Nov. So when I put 1/1/2012 in Cell A1 I need the Vat date to be 23/1/2012.When it is 5/10/2012 I need it to be the 23/11/2012.

I also have a quarterly billing problem. As above I put a date in Cell A1, and I need a formula that will give me the next 19th of the following months Mar, Jun, Sep and Dec. e.g. Cell A1 - 6/3/1978 - I need the output to be the 19/3/1978.

If you can help....I would appreciate some explanation as to the logic of the formula as I am sure I wil have to come up with more date formulas later.

As always appreciate your help on this one - Many Thanks - Mark.

 

[konew1]

Hi. You might try this for the VAT dates
=DATE(YEAR(A1)+IF(MONTH(A1)>10,1,0),CHOOSE(MONTH(A1),3,3,5,5,7,7,9,9,11,11,1,1),23).
You should chjeck that it works as required for dates in November and december.
You will be able to modify it for the quarterly date.
I suggest you also look at excel help for =EOMONTH and other date function in help

Edit: the dates in the query don't match the words, Invoice 1/1/12 due 23/1/12? Have a look at the formula above ald see if itt works.

 

[Mark McInerney]

Brilliant! Many Thanks - really appreciated.

 

[Mark McInerney]

I'm chuffed with this!!!! Thanks again!

 

[Mark McInerney]

Sorry...but I am getting a strange result when I change the date....

The #value formula is =DATE(YEAR(C191)+IF(MONTH(C191)>10,1,),CHOOSE(MONTH(C191),3,3,6,6,9,9,12,12),16) where c191 = 16/9/2012

	16/09/2012		Sunday		Bank Charges	-2,000.00
	#VALUE!		#VALUE!		Bank Charges	-2,000.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Have I messed it up?

 

[konew1]

That formula isn't what I posted. There must be 12 numbers in the CHOOSE function and you only have 8.
These numbers represent the month number of the month the vat is due.
I was thinking about EOMONTH and realised after my first reply that it is a neater solution for you.
=EOMONTH(A1,1+ISEVEN(MONTH(A1)))+23 in a cell formatted as a date. It will work for the 2-monthly VAT but may not be easily modified for the quarterly dates

 

[JimM]

Does this one work

=IF(ISEVEN(MONTH(A1)),DATE(YEAR(A1),MONTH(A1)+1,23),DATE(YEAR(A1),MONTH(A1),23))

 

[barry houdini]

A typical example is: The VAT is due on the 23rd of every second month, starting in Jan and going thru Mar, May, Jul, Sep, Nov. So when I put 1/1/2012 in Cell A1 I need the Vat date to be 23/1/2012.When it is 5/10/2012 I need it to be the 23/11/2012.

Hello Mark, what if A1 is actually one of the payment dates, like 23/1/2012, should the formula return that date, 23/1/2012...or the next one, 23/3/2012? Assuming the latter try this formula

=LOOKUP(A1,DATE(YEAR(A1),{0,1,3,5,7,9,11;1,3,5,7,9,11,13},23))

Similarly for the quarterly payments try

=LOOKUP(A1,DATE(YEAR(A1),{0,3,6,9,12;3,6,9,12,15},19))

 

[Mark McInerney]

Thank you all for your help - I am really making a mess of this!

Is there a generic formula that I can use that will be able to tweak to align with the different date schedules that may arise.
e.g. the 15th of each Mar, Jun, Sept and Dec
the 6th of the next month

So whatever value is in Cell A1 it will be able to work out the next date in the cycle?

Thanks for your help

 

[Mark McInerney]

Works a dream Jim - Thanks a lot.

Can I tweak this to align to a quarterly schedule as well.

Many Thanks - Mark.