Calculating the 'N'th DAY in a month

[ajetrumpet]

Here's a brain teaser for some of you:

I want the easiest possible way to perform the calculation that is in the title of this thread. There is some code in the repository that shows the method that I have as the easiest:

http://www.access-programmers.co.uk/forums/showthread.php?t=132163&highlight=xth

This is very manual, as it checks each day in the relevant week of the month. This is fine and dandy, and is a heck of a lot faster than other methods that I have used, as it only uses two functions per line. However, the method found in the above thread is still redunant, and I have been able to reduce each line in that code to this simple statement (querying for the 3rd Monday in January):

Weekday("1/15/" & DatePart("yyyy", MyDate)) = 2

This is the most consolidated version I could get. Here is what I have for finding (as an example) the 3rd Monday in January for any given year:

3rd Monday = 

IIf(Weekday("1/15/" & DatePart("yyyy", InputDate)) = 2, _
"1/15/" & DatePart("yyyy", InputDate), _
  IIf(Weekday("1/16/" & DatePart("yyyy", InputDate)) = 2, _
  "1/16/" & DatePart("yyyy", InputDate), _
    IIf(Weekday("1/17/" & DatePart("yyyy", InputDate)) = 2, _
    "1/17/" & DatePart("yyyy", InputDate), _
      IIf(Weekday("1/18/" & DatePart("yyyy", InputDate)) = 2, _
      "1/18/" & DatePart("yyyy", InputDate), _
    IIf(Weekday("1/19/" & DatePart("yyyy", InputDate)) = 2, _
    "1/19/" & DatePart("yyyy", InputDate), _
  IIf(Weekday("1/20/" & DatePart("yyyy", InputDate)) = 2, _
  "1/20/" & DatePart("yyyy", InputDate), _
IIf(Weekday("1/21/" & DatePart("yyyy", InputDate)) = 2, _
"1/21/" & DatePart("yyyy", InputDate), 0))))))))

Is this the best I can do folks? Isn't there anymore consolidation I can do here? I was thinking of nesting a loop inside of an IF statement, but almost positive that kind of method would be slower than the above IIF block. It would certainly be more cumbersome to sift through in the modules anyway. And for that reason alone, I don't want to do it....

Thanks for any input you guys!

 

[raskew]

Hi -

Try this:

Function NthXDay(pdate As Variant, pWDay As Integer, _
pIncrement As Integer) As Date
'coded by:  raskew

Dim dteDate As Date
Dim newDate As Date

'to call ? NthXDay(dateserial(year(date), 1, 1),vbMonday, 3)
'************************************
'Some holidays traditionally fall on the Nth Xxxday of the month.
'Need a query/function that will, given a month, year, weekday
'and increment (e.g., 1st (1), 3rd (3), last (6), etc.)
'return the specific date.
'Note: Used 6 to indicate the last increment, since no month
'will have 6 of any specified weekday.

'adjust the increment if it's more than 6
pIncrement = IIf(pIncrement > 6, 6, pIncrement)

dteDate = DateValue(pdate)

'determine first day of given month
dteDate = DateSerial(Year(dteDate), Month(dteDate), 1)
'determine first specified day of given month, e.g. vbSunday
newDate = dteDate - WeekDay(dteDate) + pWDay + IIf(WeekDay(dteDate) > pWDay, 7, 0)

'determine the nth specified day of given month
newDate = DateAdd("d", 7 * (pIncrement - 1), newDate)

'if the resulting calculation is greater than the length of the
'specified month, cycle backwards to the last specified day of the month
Do While Month(newDate) <> Month(dteDate)
   newDate = newDate - 7
Loop

NthXDay = newDate

End Function

HTH - Bob

 

[ajetrumpet]

Bob,

At first glance, I am not noticing any consolidation, but that's probably because I don't like comments.

Can you post a version of that function with just the code in it? How about taking out all of the comments and Line separators. thanks....???

 

[raskew]

Hi -

Here's a cleaned-up version (no interior comments). If you want to verify what each line does, refer to my original post.

Don't quite understand the concern about 'consolidation'. Are we talking apples and oranges here?
So, are you going to sit down with a calendar and a pencil to determine what possible dates could be included in the 3rd Monday of a given month, then write a bunch of code (e.g. Iif() statements) to consider each possibility? Come on Adam, with 2011 posts I'd hope you'd know better. Do you propose doing the same with each day of the week and each possible increment?

Function NthXDay2(pdate As Variant, _
                  pWDay As Integer, _
                  pIncrement As Integer) As Date
'*******************************************
'Purpose:    Given a month, year, weekday and
'            increment (e.g., 1st (1), 3rd (3),
'            last (6), etc.) return the specific date.
'Created by: raskew
'Inputs:     1)  ? NthXday2(dateserial(2008,2,1), vbMonday, 3)
'            2)  ? NthXday2(dateserial(2008,3,1), vbThursday, 6)
'Output:     1)  2/18/2008  -  3rd Monday in Feb 2008
'            2)  3/27/2008  -  Last Thursday in Mar 2008
'*******************************************

Dim dteDate As Date
Dim newDate As Date

   pIncrement = IIf(pIncrement > 6, 6, pIncrement)
   dteDate = DateValue(pdate)
   dteDate = DateSerial(Year(dteDate), Month(dteDate), 1)
   newDate = dteDate - WeekDay(dteDate) + pWDay + IIf(WeekDay(dteDate) > pWDay, 7, 0)
   newDate = DateAdd("d", 7 * (pIncrement - 1), newDate)
   Do While Month(newDate) <> Month(dteDate)
      newDate = newDate - 7
   Loop
   NthXDay2 = newDate

End Function

Bob

 

[ajetrumpet]

Don't quite understand the concern about 'consolidation'. Are we talking apples and oranges here?

No Bob, we're not. The fact of the matter is that I was looking for a way to write the same thing as in my first, but in a more consolidated manner. In other words, in LESS amount of code. That's what consolidation means Bob.

As you could probably tell, I put quite a bit of thought into the code block that I wrote before I even posted this...

I guess the bottom line here is: Will your code run faster than mine? Obviously it is for finding the same thing I was trying to find, but are two (or 3, or 4, etc...) of your loops faster than my "manual" code of searching through 7 consecutive days? I would think not, as your code contains more functions than mine... Is this right you think?

 

[raskew]

Adam -

Nine lines of code. Have you tried it? Please post back with examples where my posted solution doesn't work.

Bob

 

[ajetrumpet]

It's not about the solution NOT working Bob...it's about clean, efficient code that is minimized in terms of the amount of lines. And no, I haven't tried it, but I will try and replace it in the database in which I have my code working now. I will time it and see if it is faster. That's really the point of this.

Do you see what I care about here? Optimization...
Having A solution to a problem is irrelevant. I already have that, as you can see.

 

[raskew]

My apoligies. Lost it there for a moment. You're the man.

Bob

 

[WayneRyan]

Very well said, Bob.

You're the man!

Wayne

 

[pbaldy]

I'm taking out my initial comments, which were perhaps overly critical.

To the point at hand, you've made assumptions about which would be faster and asked Bob to prove his was faster than yours. Since you're the one looking for the fastest method, I would think you'd be the one willing to spend the time testing. In any case, I DID test and Bob's is significantly faster, as I would expect looking at the code. Test results in inverse order (milliseconds):

These two tests included an append to a table so I could compare the outputs
AJ: 2845
Ras: 1129

AJ: 2852
Ras: 1142

This test included a counter to make sure both did the same number of tests
AJ: 2144 27394
Ras: 457 27394

the first raw tests
AJ: 2129
Ras: 450

AJ: 2281
Ras: 481

 

[pbaldy]

Dang; slow typist comes in last place.

 

[WayneRyan]

Paul,

To be fair, I didn't do ANY thinking or tests.

You're the man !

Wayne

 

[pbaldy]

No, you were right the first time. Bob's the man for the making best snappy comeback!

Now I'm going to bed.

 

[ajetrumpet]

Well...now that the sparks have flown folks, I will do my own experiment with this and let you know the real outcome.

I wonder if I've ruined my respect around here...Hmm....

 

[ajetrumpet]

BOB,

do you have a sample that you can show me where this process of yours is working? The only thing I can get from that is possibly running a loop each time I want a variable date (as in, the 3rd Monday of January, for example).

Your code just uses too many assigned variables! But, I will see what some of my adaption of your code does, and then get back to you. I am testing it now.

 

[raskew]

Adam -

What are you aiming to accomplish? I use this function as the operating mechanism for a perpetual holiday calendar which, when fed a year, triggers an update query that computes the date of each variable holiday (i.e. falls on a particular weekday (3d Monday in January) rather than one that falls on the same date each year).

I can provide that but it may be far from what you're trying to do. Please advise.

Bob

 

[ajetrumpet]

One question first:

Where did this phrase come from: Please Advise. Is that a European thing? Just wondering...

And, as far as the accomplishment is concerned, this is not work related. But, as strange as it may sound, it is also not a "How-to" (like my "Array Randomizer"!). It is simply an example of how to calculate certain days within a given time frame. I have uploaded the file to show you the process this code will be going into. What do you think of this? (It is simply an example for others).

 

[raskew]

The origins of Please Advise?! BTSOOM! It's just always been there.

At the moment, I'm working on a laptop that has only A97. Any chance you could post your example in A97?

Thanks - Bob

 

[ajetrumpet]

I can't do that Bob. I am using A07. It's not possible with this version...is it?

 

[pbaldy]

Here you go Bob. It said it would need the references fixed once opened in 97, since I don't have them on my PC.