It is by the day of the month. the first Friday is always between the 1 - 7
of the month the 2nd Friday is always between the 8 - 14. You can use the
code below. You can use either the Select Case or the MOD formula. which
ever you understand better.


Sub getfriday()

Mydate = Day(Now())

Select Case Mydate
Case 1 To 7
MyWeek = 1
Case 8 To 14
MyWeek = 2
Case 15 To 21
MyWeek = 3
Case 22 To 28
MyWeek = 4
Case 28 To 31
MyWeek = 5
End Select
MyWeek = Int((i - 1) / 7) + 1
End Sub

" wrote:

On Aug 10, 12:02 am, Joel wrote:
This code gets the first Friday of the month as a number 1 - 5

Sub getfriday()

'subtract the day of the month from now to get to the 1st
firstdayofmonth = Now() - Day(Now) + 1
'get day of week with Sunday = 1
firstdayofweek = Weekday(firstdayofmonth)
FirstFriday = ((6 - firstdayofweek) Mod 7) + 1

End Sub



" wrote:
On Aug 9, 12:16 am, Joel wrote:
First make sure the date is in date format and not a string by checking if
1st character in the cell is a single quote (quote indicates sttring).

then it is simple

Dayofmonth = day(range("A1"))
month = month(range("A1))

" wrote:
Hi,

Suppose I have aexcelcolumn that contains the dates:

Column A ( DD/MM/YY)

09/02/07
10/02/07
etc

How do I select the column and read in each dates in each cell to
check if the date: (DD) is a friday and that the date is 6th?

Thanks.

regards,
Newbie- Hide quoted text -

- Show quoted text -

Hi ,

Now do I detemine if the date is the first friday of the month or
second friday or third friday of the month written in VBA?

rgds,
Newbie- Hide quoted text -

- Show quoted text -

Hi How do i determine the nth day of week for the range of dates in
a selected column to be first friday of the month, second thursday of
the month etc? After finding the nth day of week, how do I put the nth
day day of week in a cell correspnding to the dates. For example:

Column(A) Column(B)(Nth week) Column (C)

13/07/2007 2 2ND FRIDAY OF THE MONTH
14/07/2007 2 2ND SATURDAY OF THE MONTH

where 2 is the 2nd week of the month.
Appreciate your help.

regards,
Newbie