Every 3rd Thursday

[mboe]

I have a update macro that needs to run every 3rd Thursday of the Month. Is there a way to calculate what the date would be on that day? Or is there another solution (other then manual entry and a handy calendar)?

 

[Mile-O]

=DateSerial(Year(Date), Month(Date), 1) + IIf(5 < Weekday(DateSerial(Year(Date), Month(Date), 1)), 7 - Weekday(DateSerial(Year(Date), Month(Date), 1)) + 5, 5 - Weekday(DateSerial(Year(Date), Month(Date), 1))) + 14

 

[mission2java_78]

If Weekday(DateAdd("d", 14, Now())) = 5 Then
  MsgBox "Its thursday!"
Else
  MsgBox "Its Not!"
End If

 

[Mile-O]

Re: Re: Every 3rd Thursday

If Weekday(DateAdd("d", 14, Now())) = 5 Then
  MsgBox "Its thursday!"
Else
  MsgBox "Its Not!"
End If

That only determines if the date two weeks from today is a Thursday or not.

 

[mission2java_78]

Re: Re: Re: Every 3rd Thursday

That only determines if the date two weeks from today is a Thursday or not.

If two weeks from today is a thursday isnt that the third thursday???

 

[Mile-O]

If I call that function today then no.

mboe never said they were checking from the first Thursday of the month; they just asked how to calculate the third Thursday of a month. I suspect the idea is, on the opening of the database, to execute the update query, if the date is the third Thursday of that month.

 

[mission2java_78]

=DateSerial(Year(Date), Month(Date), 1) + IIf(5 < Weekday(DateSerial(Year(Date), Month(Date), 1)), 7 - Weekday(DateSerial(Year(Date), Month(Date), 1)) + 5, 5 - Weekday(DateSerial(Year(Date), Month(Date), 1))) + 14

Even if he wanted the third thursday of each month...all he has to do is check the third week's thursday of each month no ?

Each month can be captured, right?

Each week can be captured, right?

Each day can be captured, right?

If you've answered yes to all 3 of these....then there is a much simplier method.

 

[mboe]

WOW...I didn't mean to start a controversy! However, since you are "discussing" the easiest way to do something. What is the easiest way to do rounding (in my case to the nearest thousands) in a query? If it was excel I would just do round([field],-3) but I can't find a similiar process in a query.

 

[mission2java_78]

Whose arguing? We love challenges

Here's a function you can use for nth week for any vbDay.

http://msdn.microsoft.com/library/d...v/html/findingnthparticularweekdayinmonth.asp

 

[raskew]

Mboe,

Mile-O provided a very specific response to your stated problem.

For a more generic approach (e.g., specify a day of the week, any iteration within the month), here's an adaptation of his methodology.

Function NthXDay(pDate As Variant, pWDay As Integer, pIncrement As Integer) As Date
'*******************************************
'Name:      NthXDay (Function)
'Purpose:   Find the nth occurence of a weekday
'           within any given month, with Sunday = 1
'           through Saturday = 6
'Inputs:    To find the 3rd Thursday of Sep 2003
'           ? NthXDay(#9/24/03#, 5, 3)
'Output:    9/18/03
'*******************************************

Dim dteDate As Date, newDate As Date

'find the first day of the month
dteDate = DateSerial(Year(DateValue(pDate)), Month(DateValue(pDate)), 1)
'move to the first pWDay
newDate = dteDate - WeekDay(dteDate) + pWDay + IIf(WeekDay(dteDate) > pWDay, 7, 0)
'move to the specified (pIncrement -1) occurence of pWday
newDate = DateAdd("d", 7 * (pIncrement - 1), newDate)
NthXDay = newDate

End Function

Our resident junior space-cadet was unable to correctly interpret a very clearly written problem statement. He did however provide a shallow working solution to a problem that wasn't expressed. When this was pointed out to him, he did his usual thing--whined, argued and belittled. "Gee, this is so simple, I don't know why you folks can't do it." Then 'wimped out', never providing any solution save citing a MSKB article as his backup. Way to go, Champ! You're a true inspiration!

 

[Pat Hartman]

I posted a database earlier today with a bunch of date functions and a form that executes them. You can simply import the module into your own db and call the appropriate function or convert the db to an .mda and use it that way. The weekday in month function that I coded always returns the first date. If you want the second, third, etc. just add the appropriate number of days. The function uses a technique similar to raskew's.

Useful Date Functions

 

[mission2java_78]

Raskew crying again...

Poor guy...I sure hope his wife can stand him ...

No one said his solution was wrong...his solution may not be as easily read by most people...because of the use of IIF and the constants as well as the various functions within it.

I just crack up when I see raskew post...he seems to have such a grudge

 

[mission2java_78]

I see ol man rat-kew had to edit his post...what's wrong couldn't copy the function correctly

Amazes me ...

 

[namliam]

=DateSerial(Year(Date), Month(Date), 1) + IIf(5 < Weekday(DateSerial(Year(Date), Month(Date), 1)), 7 - Weekday(DateSerial(Year(Date), Month(Date), 1)) + 5, 5 - Weekday(DateSerial(Year(Date), Month(Date), 1))) + 14

Or my onliner:cvdate("01 " & format(date(),"mmm yyyy")) - weekday(cvdate("01 " & format(date(),"mmm yyyy")),vbfriday)+21

I never can resist doing something the same but then a little different from Mile....

Regards

The Mailman

 

[uncle-lai]

Hi,

Sorry to interrupt. But sometimes simple minded people may find the simplest way to do something.

Can this code serve the purpose?

If (Day(Date) >= 15) And (Day(Date) <= 21) And (Format(Date, "ddd") = "Thu") Then
'Run backup
End If

Use nos. 1 & 7 for 1st, 8 & 14 for 2nd etc.

Peter

 

[namliam]

Dont use format as it will depend on your local/regional settings, use weekday instead, this returns a number. Using weekday(date(),vbmonday) its independant of your regional setting. (or any other vb variable like vbsunday)

Other than that its a nice way if you dont need the exact date just the 3rd thu.

Regards

 

[Mile-O]

Yeah, the specifying of a string, i.e.:

Format(Date, "dddd") for today would return "Thursday" but were your regional settings changed, even acidentally, to German then, I'm sure the exact same function would return Donnerstag.

Using Weekday() is a more explicit method for determining a day.

 

[uncle-lai]

But how do I put the WeekDay() function in my code? I mean the correct syntex of using Weekday() in this case. I've tried but could not succeed.

Thanks for enlightening me.

Peter

 

[Mile-O]

If (Day(Date) >= 15) And (Day(Date) <= 21) And Weekday(Date) = 5 Then 
    'Run backup 
End If

 

[uncle-lai]

I got it! Thanks a lot.

Peter