Excel Problem

  • Thread starter Thread starter newbie
  • Start date Start date
N

newbie

Hi,

Suppose I have a excel column 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
 
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))
 
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))







- Show quoted text -
Click to expand...

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
 
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
 
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








- Show quoted text -
Click to expand...

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
 
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
 
Back
Top