#### Mark McInerney

##### Board Regular
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.

### Excel Facts

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

#### konew1

##### Well-known Member
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
Brilliant! Many Thanks - really appreciated.

#### Mark McInerney

##### Board Regular
I'm chuffed with this!!!! Thanks again!

#### Mark McInerney

##### Board Regular

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

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

#### Mark McInerney

##### Board Regular
Works a dream Jim - Thanks a lot.

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

Many Thanks - Mark.

Replies
1
Views
74
Replies
18
Views
477
Replies
2
Views
124
Replies
1
Views
68
Replies
4
Views
121

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.

### Which adblocker are you using?

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

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