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

Chuck

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


--


Gary K



"Chuck" wrote in message
...
How can I find the date (number) of the third wednesday of a month?

Chuck

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.

--
Rick (MVP - Excel)


"Chuck" wrote in message
...
How can I find the date (number) of the third wednesday of a month?

Chuck

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)

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Chuck" wrote in message
...
How can I find the date (number) of the third wednesday of a month?

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?

--
Rick (MVP - Excel)


"Bob Phillips" wrote in message
...
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)

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Chuck" wrote in message
...
How can I find the date (number) of the third wednesday of a month?

Chuck

On Sun, 8 Mar 2009 09:27:30 -0400, "Rick Rothstein"
wrote:

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?

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

Chuck

Bob responded with an "oops" message. But he didn't respond to his post.

Rick Rothstein wrote:

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?

--
Rick (MVP - Excel)

"Bob Phillips" wrote in message
...
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)

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Chuck" wrote in message
...
How can I find the date (number) of the third wednesday of a month?

Chuck




--

Dave Peterson

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

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
Bob responded with an "oops" message. But he didn't respond to his post.

Rick Rothstein wrote:

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?

--
Rick (MVP - Excel)

"Bob Phillips" wrote in message
...
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)

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Chuck" wrote in message
...
How can I find the date (number) of the third wednesday of a month?

Chuck




--

Dave Peterson

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

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

On Sun, 08 Mar 2009 00:11:47 -0500, Chuck wrote:

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

Chuck

With any date in A1,

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

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

On Sun, 08 Mar 2009 00:11:47 -0500, Chuck wrote:

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

Chuck

Thanks all. All equations work perfectly.

Chuck