Find date of 3rd Wed of month

G

Gary Keramidas

I have some code that may do what you want.

enter 1/1/2009 thru 12/1/2009 in row 1 columns A:L

then run this code:

Sub test()
Dim stdt As Long
Dim dt As Date
Dim wVal As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
For i = 1 To 12
dt = ws.Cells(1, i).Value
stdt = DatePart("w", DateSerial(Year(dt), Month(dt), 1), vbSunday,
_
vbFirstFullWeek)
If stdt = 4 Then
wVal = 21 + 1
ElseIf stdt > 4 Then
wVal = 21 - (stdt - 4) + 1
Else
wVal = 14 + 4 - stdt + 1
End If
ws.Cells(2, i).Value = wVal
Next
End Sub
 
R

Rick Rothstein

Try this formula....

=DATE(YEAR(A1),MONTH(A1),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))

where A1 is assumed to contain any date within the month of interest. Of
course, you may substitute the direct year and month for both of the
YEAR(A1) and MONTH(A1) function calls.
 
C

CurlyDave

Place thed 1st of the month in A2 this will give the 3rd Wednesday of
that month

=A2+MOD(4-WEEKDAY(A2),7)+14
 
B

Bob Phillips

I don't think this works if the 1st day of the month is a Wed.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Ron Rosenfeld

How can I find the date (number) of the third wednesday of a month?

Chuck
Click to expand...

With any date in A1,

=A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+4)

will return the third Wednesday of that month.
--ron
 
R

Rick Rothstein

I just tried it on a couple of April 2009 dates (April 1, 2009 is a
Wednesday) and it worked... can you show me an example month where it fails?
 
R

Rick Rothstein

Ah, I see his "oops" message now... I didn't associate it with this
sub-thread when I first read it.
 
C

Chuck

I just tried it on a couple of April 2009 dates (April 1, 2009 is a
Wednesday) and it worked... can you show me an example month where it fails?
Click to expand...

Thanks for the code Rick. April 1, 2009 works for me too.

Chuck
 
B

Bob Phillips

That's because I posted it before the original arrived in my NG client Rick.
Keen to correct my error <g>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Rick Rothstein said:
Ah, I see his "oops" message now... I didn't associate it with this
sub-thread when I first read it.
Click to expand...
 
Top