Construct Projected Date

[Dan2kx]

Hello to all,

what i would like to do is have a bit of VB code to set a variable to the next tax year but am having trouble constructing it..

Taxdate = 01/04/year+1

so if this year 2008 then i want the taxt year to return the first monday around 01/04/2009
taking into account that if today was after new year the next taxdate would be the same... any ideas???

thanks for the interest

[patjones]

Hello to all,

what i would like to do is have a bit of VB code to set a variable to the next tax year but am having trouble constructing it..

Taxdate = 01/04/year+1

so if this year 2008 then i want the taxt year to return the first monday around 01/04/2009
taking into account that if today was after new year the next taxdate would be the same... any ideas???

thanks for the interest

Look in the Access help pages for the DateAdd function. It has three arguments: the time interval you are working with (e.g. days, months, etc.), how many of those intervals you want to add, and the date you want to add to.

For instance, to add 25 days to May 14th, 2008, you could write DateAdd("d", 25, "14-May-2008"). For you, the only twist is that the number of intervals would have to be calculated depending on what the current date is, which you could probably use DateDiff for...

Pat

[Dan2kx]

Its unfortunately the twist that i cant wrap my head around...

i would have to work out the date before i could work out the difference to work out the date, doesnt make sense does it is it possible to do something like this


if month(date) between 04 and12 then x = 1 else x = 0
taxdate = day(01)+month(04)+year(x)

[patjones]

Its unfortunately the twist that i cant wrap my head around...

i would have to work out the date before i could work out the difference to work out the date, doesnt make sense does it is it possible to do something like this


if month(date) between 04 and12 then x = 1 else x = 0
taxdate = day(01)+month(04)+year(x)

OK, try this:

 Dim intCurrentMonth As Integer 
Dim intCurrentYear As Integer
Dim intNextYear As Integer
 
Dim intNumOfDaysToAdd As Integer
 
Dim dteTaxDate As Date
 
intCurrentMonth = DatePart("m", Now)
intCurrentYear = DatePart("yyyy", Now)
 
intNextYear = intCurrentYear+1
 
If intCurrentMonth >=4 And intCurrentMonth <=12 Then
 
     intNumOfDaysToAdd = DateDiff("d", Now, "1-April-" & intNextYear)     
 
Else
 
     intNumOfDaysToAdd = DateDiff("d", Now, "1-April-" & intCurrentYear)
 
EndIf
 
dteTaxDate = DateAdd("d", intNumOfDaysToAdd, Now)
 

This is actually quick and dirty, because I'm using the VB canned date functions, but it seems to work with the examples I tried out in my Immediate Window...

Pat

[Dan2kx]

genius that seems to work thanks!!

[patjones]

genius that seems to work thanks!!

It's no problem. And one other thing...when I ran it in my Immediate Window, I found that it returned dteTaxDate with the time of day part also. You might want to take action to get rid of that, depending on what your need is.

Pat

[Dan2kx]

substituting "now" for "date" solved that problem

thanks again

[NeoPa]

=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))

[patjones]

=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))

Now I'm eating my words "quick and dirty"...

[NeoPa]

Not so fast Pat.

My solution doesn't find the Monday, it just finds the 1st of the month.

[patjones]

Not so fast Pat.

My solution doesn't find the Monday, it just finds the 1st of the month.

And neither does mine! Yikes...

This would find the first Monday after the tax date:

 Dim dteTaxDate As Date 
 
dteTaxDate=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))
 
while DatePart("w",dteTaxDate) <> vbMonday
     dteTaxDate = DateAdd("d", 1, dteTaxDate)
wend
 

Pat

[NeoPa]

You can use the Weekday() function arithmetically to go straight to the correct date value :

Public Function TaxDate() As Date
  TaxDate = CDate("1 April " & Year(Date()) + IIf(Month(Date()) < 4, 0, 1))
  TaxDate = TaxDate + 7 - Weekday(TaxDate, vbTuesday)
End Function

PS. The reason this is difficult (more complicated) in SQL is that the 1st April calculation would need to appear twice.

[patjones]

And neither does mine! Yikes...

This would find the first Monday after the tax date:

 Dim dteTaxDate As Date 
 
dteTaxDate=CDate("1 April " & Year(Date())+IIf(Month(Date())<4,0,1))
 
while DatePart("w",dteTaxDate) <> vbMonday
     dteTaxDate = DateAdd("d", 1, dteTaxDate)
wend
 

Pat

I tried this out with several dates and it seems to work nicely, finding the nearest Monday to April 1st...

 Dim dteTaxDate As Date 
Dim a As Integer
 
dteTaxDate = CDate("1 April " & Year(#12/6/2007#) + IIf(Month(#12/6/2007#) < 4, 0, 1))
 
a = DatePart("w", dteTaxDate, vbMonday) - 1
 
dteTaxDate = IIf(a <= 3, DateAdd("d", -a, dteTaxDate), DateAdd("d", 7 - a, dteTaxDate))
 

Pat

[FishVal]

Hello, gentlemen.

It seems to me that the logic has a bug.
What will happen if current date is Sunday, 2 April?

[NeoPa]

Then you get Monday 2nd April (2007).

I tested it (my code) for Sunday 2nd April 2006.

PS. Did you mean gentlemen? Or were you talking only of Pat's code?

[FishVal]

Then you get Monday 2nd April (2007).

I tested it (my code) for Sunday 2nd April 2006.

That is actually what I've meant. Don't you think Monday 3rd April 2006 would be the right answer?

[NeoPa]

That makes a certain sense Fish, but from post #3, I would say no.

[FishVal]

That makes a certain sense Fish, but from post #3, I would say no.

Oh. Post #3 is not about "monday issue" at all. ;)
And, come on guys, don't you like this more challenging riddle?

[NeoPa]

Oh. Post #3 is not about "monday issue" at all. ;)

Not sure I'd agree about post #3 there Fish. It's the OP's request spelled out is all. It's all one request.

And, come on guys, don't you like this more challenging riddle?

However, there may well have been confusion and, as a general rule, it's more fun dealing with the more complex formulae so we can look at it (the more challenging riddle) anyway.

I frankly can't see a way to subsume this new version into the existing logic, nor even produce new logic that incorporates this as a part of the whole. The best I can do at this time is to add a separate test at the front, which works fine, but isn't really very interesting I'm afraid.

If I can come up with a more unified (interesting) solution I will.

[patjones]

Hello, gentlemen.

It seems to me that the logic has a bug.
What will happen if current date is Sunday, 2 April?

I tested my code (well, really it's partly NeoPa's code also) in Post 13 with 4/2/2006 as the current date and it gives me 4/2/2007, which seems correct. Looking at Post 1, he wanted the closest Monday around April 1st, which 4/2/2007 satisfies...

The code that NeoPa wrote out with the IIf statement:

dteTaxDate = CDate("1 April " & Year(#4/2/2006#) + IIf(Month(#4/2/2006#) < 4, 0, 1))
 

will advance the year by 1 so long as the current date falls on or after 4/1.

Pat

[NeoPa]

Pat, the point is that the succeeding 1st Monday of April would actually be Monday 3rd April 2006.

...If you assume that "any date before April" actually means "any date before the first Monday in April".

[patjones]

Pat, the point is that the succeeding 1st Monday of April would actually be Monday 3rd April 2006.

...If you assume that "any date before April" actually means "any date before the first Monday in April".

Yes, I understand that, but my impression from Dan2kx's posts is that it should advance to the next year, even with a date like 4/2/2006. But we'd need him to clarify that, and he probably stopped reading like 15 posts ago! :-)

Pat

[NeoPa]

Absolutely right Pat ;)