TAX Calculator Date Query...Please Help

Mark McInerney

Board Regular
Joined
Apr 4, 2012
Messages
210
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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.
 
Last edited:

Mark McInerney

Board Regular
Joined
Apr 4, 2012
Messages
210

ADVERTISEMENT

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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

Well-known Member
Joined
Nov 11, 2003
Messages
696

ADVERTISEMENT

Does this one work

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Apr 4, 2012
Messages
210
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

Board Regular
Joined
Apr 4, 2012
Messages
210
Works a dream Jim - Thanks a lot.

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

Many Thanks - Mark.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,203
Messages
5,623,348
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top