Every 2nd Tuesday of the Month

K

KC

I'm after a formular that will return the second Tuesday of each
month, extending down from a start date in A1. That start date will
be the 2nd Tuesday of Jan 05 (11 Jan 05).

I tried to fill down feature, but that doesn't work.

Cheers, Kaye
 
J

Jason Morin

Put this in A2, press ctrl/shift/enter, and fill down:

=SMALL(IF(WEEKDAY(ROW(INDIRECT(DATE(2005,ROW(),1)&":"&DATE
(2005,ROW()+1,0))))=3,(ROW(INDIRECT(DATE(2005,ROW(),1)
&":"&DATE(2005,ROW()+1,0))))),2)

HTH
Jason
Atlanta, GA
 
S

Sandy Mann

KC

With 11 Jan 05 in L1 I came up with this formula:

=DATE(YEAR(L1),MONTH(L1)+1,13)-WEEKDAY(DATE(YEAR(L1),MONTH(L1)+1,13),3)+1

(entered normally) which when copied down does give the 2nd Tuesday of each
month but I prefer the formula I found that Peter Dorigo posted in 2000:


=DATE(YEAR(L1),MONTH(L1)+1,15)-WEEKDAY(DATE(YEAR(L1),MONTH(L1)+1,5))

He also included an explanation:

Start of Peter's post *****************************

for any weekday in any week:
=DATE(YEAR(A1),MONTH(A1)+1,[w])-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,[wd]))

replace [wd] by
0 for sunday
1 for saturday
2 for friday
3 for thursday
4 for wednesday
5 for tuesday
6 for monday

replace [w] by
8 for first week
15 for second week
22 for third week
29 for fourth week

for the last week use this formula (in some cases the same result like 4.
week):
=DATE(YEAR(A1),MONTH(A1)+2,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,[wd]))

Peter
End of Peter's post **********************************


HTH

Sandy
 
B

Bob Phillips

Here is another, assuming start date in A1, add this to A2 and copy down

=DATE(YEAR(A1),MONTH(A1)+1,1+7*2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-3))

--

HTH

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

Don Guillett

Here is something that is a bit different that may help. It will calculate
the next Tuesday from a date in B2 where the date was a Friday.
=B2+IF(WEEKDAY(B2)=6,4,3)
Here is another different way that uses choose
=H2+CHOOSE(WEEKDAY(H2),1,1,1,1,1,1,2)
Just some more stuff to play with.
 
M

Myrna Larson

The 2nd Tuesday in Jan, 2004 is the 13th; for 2005 it's the 11th.

The 2nd Tuesday occurs between the 8th and the 14th, inclusive. The 2nd
Tuesday of the following month is either 28 or 35 days later.

Assuming you have the correct date for the 2nd Tuesday of some month in A1,

=IF(AND(DAY(A1+28)>=8,DAY(A1+28)<=14),A1+28,A1+35)
 
R

Ron Rosenfeld

I'm after a formular that will return the second Tuesday of each
month, extending down from a start date in A1. That start date will
be the 2nd Tuesday of Jan 05 (11 Jan 05).

I tried to fill down feature, but that doesn't work.

Cheers, Kaye
Click to expand...

Well, with 11 Jan 05 in G1, put this formula in G2 and Fill Down:

=G1-DAY(G1+30)+45-WEEKDAY(G1+35-DAY(G1+30))


--ron
 
Top