Hello,

I need to figure out all the 2nd and 4th wednesdays for all year long.
These days are set and are used for the same purpose (training) so I
would like to be able to print a list instead of hunting down a
calendar and counting. Any help would be greatly appreciated!

Thank you.....mitch

Take a look he

http://cpearson.com/excel/DateTimeWS.htm#NthDoW

In article .com,
"lawdoggy" wrote:

I need to figure out all the 2nd and 4th wednesdays for all year long.
These days are set and are used for the same purpose (training) so I
would like to be able to print a list instead of hunting down a
calendar and counting. Any help would be greatly appreciated!

JE McGimpsey wrote...
Take a look he

http://cpearson.com/excel/DateTimeWS.htm#NthDoW
....

Chip's formulas work, but they're longer than necessary. The 2nd and
4th Wednesdays in the month containing the date given in A3, one could
use

=A3-DAY(A3)+14-WEEKDAY(A3-DAY(A3)+5,3)

and

=A3-DAY(A3)+28-WEEKDAY(A3-DAY(A3)+5,3)

rather than

=A3-DAY(A3)+1+((2-(4=WEEKDAY(A3-DAY(A3)+1)))*7)+(4-WEEKDAY(A3-DAY(A3)+1))

and

=A3-DAY(A3)+1+((4-(4=WEEKDAY(A3-DAY(A3)+1)))*7)+(4-WEEKDAY(A3-DAY(A3)+1))

the latter 2 being the most compact way to render Chip's formulas given
a date in cell A3. The 5 term in the first 2 formulas corresponds to
Wednesday by counting back from Sunday = 1, so Saturday = 2, etc.

I understand most of this formula, except for the number "5" in the weekday
function. Could someone please explain what purpose it serves? How would
it change if you were looking for the 2nd Tuesday, for example?

Thanks in advance.

"Harlan Grove" wrote in message
oups.com...

<snip The 2nd Wednesday in the month containing the date given in A3, one
could
use

=A3-DAY(A3)+14-WEEKDAY(A3-DAY(A3)+5,3)

Lewis Clark wrote...
I understand most of this formula, except for the number "5" in the weekday
function. Could someone please explain what purpose it serves? How would
it change if you were looking for the 2nd Tuesday, for example?
....

Reread the *WHOLE* response, especially the final sentence: "The 5 term
in the first 2 formulas corresponds to Wednesday by counting back from
Sunday = 1, so Saturday = 2, etc." So the 2nd Friday would be given by

=A3-DAY(A3)+14-WEEKDAY(A3-DAY(A3)+ 3 ,3)

the 4th Monday by

=A3-DAY(A3)+28-WEEKDAY(A3-DAY(A3)+ 7 ,3)

and the 3rd Sunday by

=A3-DAY(A3)+21-WEEKDAY(A3-DAY(A3)+ 1 ,3)

So in general,

=SomeDate-DAY(SomeDate)+WkNum*7-WEEKDAY(SomeDate-DAY(SomeDate)+ WkDay
,3)

where WkDay is given by the table

1 Sunday
2 Saturday
3 Friday
4 Thursday
5 Wednesday
6 Tuesday
7 Monday

I apologize for not asking my question more clearly. I did read the whole
response, but I'm still missing some of the logic. If I understand
correctly:

a) The first part of the formula: "A3-DAY(A3)+14" will always return the
date (or more correctly the date serial number) of the 14th of the month
referenced in cell A3.

b) The first part of the weekday function: "A3-DAY(A3)" will reference the
last day of the previous month, which can be any day of the week. This will
always be the same day of the week as the 14th of the current month.

Here's what I don't understand: Since the last day of the previous month
can be any day of the week, I can't figure out how you know in advance what
correction to make with the WkDay term.

Thank you.



"Harlan Grove" wrote in message
oups.com...
Lewis Clark wrote...
I understand most of this formula, except for the number "5" in the
weekday
function. Could someone please explain what purpose it serves? How would
it change if you were looking for the 2nd Tuesday, for example?
...

Reread the *WHOLE* response, especially the final sentence: "The 5 term
in the first 2 formulas corresponds to Wednesday by counting back from
Sunday = 1, so Saturday = 2, etc." So the 2nd Friday would be given by

=A3-DAY(A3)+14-WEEKDAY(A3-DAY(A3)+ 3 ,3)

the 4th Monday by

=A3-DAY(A3)+28-WEEKDAY(A3-DAY(A3)+ 7 ,3)

and the 3rd Sunday by

=A3-DAY(A3)+21-WEEKDAY(A3-DAY(A3)+ 1 ,3)

So in general,

=SomeDate-DAY(SomeDate)+WkNum*7-WEEKDAY(SomeDate-DAY(SomeDate)+ WkDay
,3)

where WkDay is given by the table

1 Sunday
2 Saturday
3 Friday
4 Thursday
5 Wednesday
6 Tuesday
7 Monday

In article .com,
"Harlan Grove" wrote:

Chip's formulas work, but they're longer than necessary. The 2nd and
4th Wednesdays in the month containing the date given in A3, one could
use

=A3-DAY(A3)+14-WEEKDAY(A3-DAY(A3)+5,3)

and

=A3-DAY(A3)+28-WEEKDAY(A3-DAY(A3)+5,3)

Might could even make it a bit shorter:

2nd Wed:

J1: =A3 - DAY(A3) + 14 - WEEKDAY(A3 - DAY(A3) + 5, 3)

4th Wed:

J2: =J1+14

In A1:A12, enter the first day of each month.

In B1:

=A1-WEEKDAY(A1)+11+(WEEKDAY(A1)4)*7

In C1:

=B1+14

Copy B1 and C1 down to the end of the list.

Tim C

"lawdoggy" wrote in message
oups.com...
Hello,

I need to figure out all the 2nd and 4th wednesdays for all year long.
These days are set and are used for the same purpose (training) so I
would like to be able to print a list instead of hunting down a
calendar and counting. Any help would be greatly appreciated!

Thank you.....mitch

On 4 Aug 2005 08:46:42 -0700, "lawdoggy" wrote:

Hello,

I need to figure out all the 2nd and 4th wednesdays for all year long.
These days are set and are used for the same purpose (training) so I
would like to be able to print a list instead of hunting down a
calendar and counting. Any help would be greatly appreciated!

Thank you.....mitch

With any date of a month in A1:

2nd Wednesday:

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

4th Wednesday:

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


--ron

I took a somewhat different approach. I figured that the user would
enter "Year" only A1, and from there want to know all of his 2nd and
4th Wednesdays in a list.

So the first thing is to find which day (between 8 and 14) is the 2nd
Wednesday of January of Year value stored in A1.

After that, a single formula figures out each other date in the list.

The first formula I used (at cell E1) is:
=DATE( A1, 1, 8) + 4 - WEEKDAY( DATE( A1, 1, 8)) + IF( WEEKDAY( DATE(
A1, 1, 1)) 4, 7, 0)
(The 4 value represents the day of the week we want, Wednesday.
Substituting other values from 1 to 7 would give the other days of the
week, and would re-figure the remaining days in the list to be same day
of week.)

The next formula, used to generate the rest of the list, is:
=IF( DAY( E1 + 14) = 8, E1 + 14, E1 + 21)

Copy that one down the next 23 rows and you have the entire list of
dates.

Chris

Thanks for all of your help! I wish I had just half of your guy's math
brain power:)

mitch