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.

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

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

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

Lewis Clark wrote...
....
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.

WEEKDAY(x,3) returns numbers between 0 and 6 no matter what x is (for x
0). Since y-DAY(y)+14 is the 14th day of the month, it's the last day in the month that could be the 2nd particular weekday in the month. In order to get the particular weekday desired, it may be necessary to back up from the 14th day of the month.

At that point it's a simple one-to-one relation. Here's a mapping of
the offsets needed by weekday of the 14th of the month and weekday
desired.

WkDay Desired
wkDay of 14th Mo Tu We Th Fr Sa Su
Mo 0 6 5 4 3 2 1
Tu 1 0 6 5 4 3 2
We 2 1 0 6 5 4 3
Th 3 2 1 0 6 5 4
Fr 4 3 2 1 0 6 5
Sa 5 4 3 2 1 0 6
Su 6 5 4 3 2 1 0

This shows a regular pattern. The key here is realizing that the offset
would be the same no matter which day of the week the 14th day of the
month was, so it's only necessary to figure out what offset would be
needed when Monday were the 14th day of the month. When the 14th is
Monday and the weekday desired is Monday, no offset needed, so add or
subtract 0 or 7. If Tuesday were desired, add 6 or subtract 1. If
Wednesday were desired, add 5 or subtract 2. Etc.

I chose to use additive offsets because they won't generate date values
< 0 when y is any valid date from 1-Jan-1900 through 24-Dec-9999.
Subtracing offsets could cause problems with dates before 7-Jan-1900.
Since it's slightly more likely people these days would be using dates
near the turn of the 20th century rather than near the end of the 99th
century, additive offsets would be slightly safer.

That helped. Thank you very much.



"Harlan Grove" wrote in message
oups.com...
Lewis Clark wrote...
...
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.

WEEKDAY(x,3) returns numbers between 0 and 6 no matter what x is (for x
0). Since y-DAY(y)+14 is the 14th day of the month, it's the last day in
the month that could be the 2nd particular weekday in the month. In order
to get the particular weekday desired, it may be necessary to back up
from the 14th day of the month.

At that point it's a simple one-to-one relation. Here's a mapping of
the offsets needed by weekday of the 14th of the month and weekday
desired.

WkDay Desired
wkDay of 14th Mo Tu We Th Fr Sa Su
Mo 0 6 5 4 3 2 1
Tu 1 0 6 5 4 3 2
We 2 1 0 6 5 4 3
Th 3 2 1 0 6 5 4
Fr 4 3 2 1 0 6 5
Sa 5 4 3 2 1 0 6
Su 6 5 4 3 2 1 0

This shows a regular pattern. The key here is realizing that the offset
would be the same no matter which day of the week the 14th day of the
month was, so it's only necessary to figure out what offset would be
needed when Monday were the 14th day of the month. When the 14th is
Monday and the weekday desired is Monday, no offset needed, so add or
subtract 0 or 7. If Tuesday were desired, add 6 or subtract 1. If
Wednesday were desired, add 5 or subtract 2. Etc.

I chose to use additive offsets because they won't generate date values
< 0 when y is any valid date from 1-Jan-1900 through 24-Dec-9999.
Subtracing offsets could cause problems with dates before 7-Jan-1900.
Since it's slightly more likely people these days would be using dates
near the turn of the 20th century rather than near the end of the 99th
century, additive offsets would be slightly safer.

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