2nd and 4th Wednesdays formula?

[lawdoggy]

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

[lawdoggy]

[Bob Phillips]

If you mean that you want every 14 days from a given Wednesday date, try
this

In A1, input your start date
In A2, input =A1+14
Then copy A2 down as far as you want to go.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"lawdoggy" <lawdo...@hotmail.com> wrote in message
news:1123170452....@f14g2000cwb.googlegroups.com...

[JE McGimpsey]

Take a look here:

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

In article <1123170402.2...@g47g2000cwa.googlegroups.com>,

[Gary's Student]

If your month is in cell C31 (4 for April as an example) then

=CHOOSE(WEEKDAY(DATE(2005,C31,1)),10,9,8,7,13,12,11)+DATE(2005,C31,1)

will return the date of the second Wednesday in that month; to get the
fourth Wednesday, just add 14 to each of the CHOOSE arguments
--
Gary's Student

[Harlan Grove]

JE McGimpsey wrote...

>Take a look here:
>
> 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.

[Tim C]

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" <lawdo...@hotmail.com> wrote in message

news:1123170402.2...@g47g2000cwa.googlegroups.com...

[@yahoo.com Lewis Clark]

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" <hrl...@aol.com> wrote in message
news:1123175552.3...@g47g2000cwa.googlegroups.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)

[Harlan Grove]

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

[@yahoo.com Lewis Clark]

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" <hrl...@aol.com> wrote in message

news:1123182979.4...@z14g2000cwz.googlegroups.com...

[JE McGimpsey]

In article <1123175552.3...@g47g2000cwa.googlegroups.com>,
"Harlan Grove" <hrl...@aol.com> 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

[Ron Rosenfeld]

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

[Harlan Grove]

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.

[@yahoo.com Lewis Clark]

That helped. Thank you very much.

"Harlan Grove" <hrl...@aol.com> wrote in message

news:1123271597.1...@g47g2000cwa.googlegroups.com...

[Blue Hornet]

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

[lawdoggy]

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

mitch