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.

 

[Mark McInerney]

Thanks Barry - Will try the quarterly one now - Appreciated.

Your assumption is correct - it will fall on the next one 0 in your example it would be 23/3/2012.

Thanks again.

 

[Mark McInerney]

That's perfect Barry - Thanks. Brilliant!

Can you advise on how I work something that will go to the next nth of a month and if this is a Sat or Sun it goes to the next working day - Monday?

 

[Mark McInerney]

Hi Jim - Barry's formula for the quarterly worked. Thanks.

 

[barry houdini]

If you look at the formulas I suggested there is quite a simple pattern, e.g. with this one

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

the 19 at the end is the day of the month.....

The blue/red numbers are the months, blue and red are separated by a semi-colon, all the rest by commas. The blue numbers are your payment months preceded by zero and the red numbers are the payment month again....followed by effectively the next payment month (month 15 of this year is the same as March next year)

....so if you wanted payments on 13th of the month in January, June and August only that would be

=LOOKUP(A1,DATE(YEAR(A1),{0,1,6,8;1,6,8,13},13))

For the next 6th of the month you can use a simpler version like this

=DATE(YEAR(A1-5),MONTH(A1-5)+1,6)

change 5 & 6 dependant on day of the month

....and to get that on a weekday, always, amend to

=WORKDAY(DATE(YEAR(A1-5),MONTH(A1-5)+1,6)-1,1)

 

[Mark McInerney]

I am using this formula to work out the nth of the month:
=(WORKDAY(EOMONTH(C1,0)+nth,0))

The problem I have is that when cell C1 is = 2/10/2012, and n = 5, the formula throws out 5/11/2012 and not the 5/10/2012 which is what it should be?

Appreciate any thoughts. Mark.

 

[Mark McInerney]

Thanks Barry - Will try those out.

Genuinely appreciate your explaining the logic - very useful. Many, Many Thanks!

 

[barry houdini]

Just to re-iterate the generic version.....

=WORKDAY(DATE(YEAR(A1-n+1),MONTH(A1-n+1)+1,n)-1,1)

or with EOMONTH as you were using

=WORKDAY(EOMONTH(A1-n+1,0)+n-1,1)

Note: =WORKDAY(date,0) = date - if date is a Saturday or Sunday then that date will still be returned

 

[Mark McInerney]

Hi Barry - I'm having some trouble with the formula. Can you advise if possible? Thanks.

=WORKDAY(DATE(YEAR($C$1-5),MONTH($C$1-5)+1,K83)-1,1)

Where c1= 05/01/2012
k83 = 3

The result of this is 3/1/2012? I need to to be the 3/2/2012.

Not sure if relevant but when c1 is 01/01/2012, the result is correct 3/1/2012.

 

[JimM]

please ignore, posted in error

 

[Mark McInerney]

Apologies Barry - had keyed your formula incorrectly.

It's perfect - Thanks all. Appreciated.

Mark.