NOT Particular Weekday in a Month but the opposite

[Divit]

I want my code to tell me that (today) April 12th is the second
wednesday of the month. And only thing I can find is the function to
tell me what date belongs to the 2nd day in a given month and year.

Please help
regards
ScuBart

[pietlinden]

you'd have to write a function to do it. Do something like...

1. get the day of the first day of the month.
2. find the number of weeks between the two dates
3. then from that you should be able to get the number of Wednesdays or
whatever.

Look up DateAdd, DateDiff, MOD, and that should do it.

[Rich P]

Plant this code in a standard code module. Place your mouse cursor
inside of someSub(). Press the F5 key. This will display the given day
which the built-in Date() function returns and if it is the 1st, 2nd,
3rd... occurence of that day in that month. You can use any date value
for the argument.

'------------------------------------------------------
Sub someSub()
Dim d1 As Date, arrSuffix As Variant
arrSuffix = Array("", "1st", "2nd", "3rd", "4th", "5th")
d1 = Date '--d1 can be any date you want: d1=#3/13/06#
Debug.Print arrSuffix(DayOf MonthCount(d1)) & " " & _
WeekdayName(Wee kday(d1))
End Sub
Function DayOfMonthCount (d1 As Date) As Integer
Dim i As Integer, j As Integer, k As Integer, d2 As Date
d2 = d1 - Day(d1) + 1 '--go to beginning of d1
k = 0
'--get count of days in the month of d1
j = Day(DateAdd("m" , 1, d1) - Day(DateAdd("m" , 1, d1)))
For i = 0 To j
'--get count of times the d1 day occurs in the month of d1
If Weekday(d2 + i) = Weekday(d1) Then k = k + 1
If d2 + i > d1 Then Exit For
Next
DayOfMonthCount = k
End Function
'----------------------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Divit]

Rich,
I may need a little bit of help. I've put the code in a module and from
the Visual basic Editor I can step through it. I've put in a msgbox to
see what the result is and it says that today is the second friday
(instead of thursday) but that has something to do with monday being my
first day and normal is sunday.

Now; when I open een a form I display the date, the weeknumber and in a
subform the actions that belong to this given date or day.

We have actions on every monday and actions for the first monday of the
month (and wednesdays and thursday to). So When I open the form I'll
have to caal the function to put in to a text box for example if this
is the second monday (or wednesday or thursday).

Can you provide me with extra hints!
Thanks so far

[kaniest]

Rich P schreef:

Function DayOfMonthCount (d1 As Date) As Integer

Using integer division operator "\", the code can be s
implified a little:

DayOfMonthCount = 1 + (Day(d1) - 1) \ 7

--
Paul

[Divit]

Sometimes people whoe are an expert in things are very cryptic for
people who are reasonable new in this stuff.

Paul, I appreciate the help but I have no idea what you mean.

On opening my form I've got in a field "second thursday" and thats what
i wanted. Now the rest of this challenge!!

[kaniest]

Divit

On opening my form I've got in a field "second thursday" and thats what
i wanted. Now the rest of this challenge!!

My code only replaces Rich's version of the calculation.
I'm not shure what it is that your trying to do.
Maybe you want to know the exact date of the second
thursday in a certain month of a certain year?

[Divit]

No, as I explained I want to know what "day" today is. It's april 13th
and more important it's the second thursday of the month. I want to
build an application to do my planning. We have actions that occur
every monday for example but also actions in even-weeks on wednesday.

So I have to determine what "day" todat is.

I solved the "second thursday of april" problem and ofcourse the day
and the weeknumbers where no problem. Now I have to figure how to
combine my 2 tables. 1 with actions and the other with occurences.

Hope you got it.
Bart

[kaniest]

Divit schreef:

No, as I explained I want to know what "day" today is. It's april 13th
and more important it's the second thursday of the month. I want to
build an application to do my planning. We have actions that occur
every monday for example but also actions in even-weeks on wednesday.

So I have to determine what "day" todat is.

I solved the "second thursday of april" problem and ofcourse the day
and the weeknumbers where no problem. Now I have to figure how to
combine my 2 tables. 1 with actions and the other with occurences.

Hope you got it.

Well ... let me rephrase.

One table specifies for each action a pattern of calendar dates -
monday of an even week, 2nd thursday of a month, etc.

Given a date range FromDate..ToDat e you can derive a
planning of actions on actual dates in that range.
That's what your after, or?

What 2nd table do you mean? Some technical solutions
use a table of all dates in the range. Is that the one?

[Lyle Fairfield]

A little fun function for morning coffee.

Public Function Whatever(ByVal d As Date) As String
Dim a As Variant
a = Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th")
Whatever = a((Day(d) - 1) \ 7) & " " & WeekdayName(Wee kday(d),
True, vbSunday)
End Function

Works for April 2006. Not tested otherwise.
Done in Access 2003. Not sure in what version the WeekdayName function
was given to us. Second Parameter ("True") of WeekdayName function
determines abbreviations or full name.