3rd Friday every month

[Guest]

Happy Sunday everybody!
I'm an options trader and in my live the 3rd Friday of the month its
important. If I have in cell A1 a date (opening the account), what formula do
I have to use to see the next 3rd friday date in cell C2 and the next in C3,
C4 etc. Example A1 = 01/14/2007 than in C2 I like to see 01/19/2007 in C3
02/16/2007. Please help me.

 

[Don Guillett]

this will find the third friday from any valid date in cell t1
=T1-DAY(T1)+8+14-WEEKDAY(T1-DAY(T1)+2)

 

[Guest]

This formula its working in C3 but not in C4, C5, C6 etc. In C4 or higher it
brings the same reults 1/19/2007

--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

this will find the third friday from any valid date in cell t1
=T1-DAY(T1)+8+14-WEEKDAY(T1-DAY(T1)+2)

 

[Gord Dibben]

Assuming real dates, different months in C4 and etc.

Perhaps your calculation mode is set to "manual"

Tools>Options>Calculation


Gord Dibben MS Excel MVP

 

[Don Guillett]

You could use in c3 and in the others c3+7 and copy down
--
Don Guillett
SalesAid Software
(e-mail address removed)

This formula its working in C3 but not in C4, C5, C6 etc. In C4 or higher
it
brings the same reults 1/19/2007

 

[Guest]

Calculation = Automatic Thanks. I like to ask you please to think over one
more time the problem. A1 = today's date 01/14/2007 . I like to see the
result in C3 = the next 3rd Friday of the month (it can be the same month
like today). In C4 if A1 = 01/14/2007 I like to see the 3rd Friday of the
next month, in C5 the following moth 3rd Friday. If its not to much problem I
like to ask a formula what I copy from C4 and only paste into the rest of the
column.

 

[Guest]

I think I dont understand something. A1= 01/114/2007.
C3 =A1-DAY(A1)+8+14-WEEKDAY(A1-DAY(A1)+2 RESULT 01/19/2007
C4 =A1-DAY(A1)+8+14-WEEKDAY(A1-DAY(A1)+2 RESULT 01/20/2007

 

[Guest]

Please dont give up on me!

 

[Guest]

I'm sure there's a simple way Zsolt but I think this will work for you

In C2

=DATE(YEAR(A1),MONTH(A1)+(22-WEEKDAY(A1-DAY(A1)+2)<=DAY(A1)),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+(22-WEEKDAY(A1-DAY(A1)+2)<=DAY(A1)),2))

in C3 copied down

=IF(DAY(C2+14)<8,28,35)+C2

 

[Guest]

Corrected from my previous reply

C2
=20-WEEKDAY(DATE(YEAR($A$1),MONTH($C$1)+ROWS($1:1)-1,1))+DATE(YEAR($A$1),MONTH($C$1)+ROWS($1:1)-1,1)

Copy from C2 all the way down as far as needed

 

[Don Guillett]

Put the formula in c2 and in c3 =c2+7 in c4 =c3+7,etc

 

[David Biddulph]

But he doesn't want the next week in the next row, he wants the 3rd Friday
of the next month.

 

[Ron Rosenfeld]

Happy Sunday everybody!
I'm an options trader and in my live the 3rd Friday of the month its
important. If I have in cell A1 a date (opening the account), what formula do
I have to use to see the next 3rd friday date in cell C2 and the next in C3,
C4 etc. Example A1 = 01/14/2007 than in C2 I like to see 01/19/2007 in C3
02/16/2007. Please help me.

C2: =A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+2)
C3: =C2+54-DAY(C2+32)-WEEKDAY(C2+6-DAY(C2+32))

select C3 and copy/drag down as far as needed


--ron

 

[Guest]

Hello Zsolt,

I assume that if your account opening date in A1 is on or after the 3rd
Friday of a month that C2 should return the 3rd Friday of the NEXT month,
that's what my suggested formula does

I don't think Teethless Mama's formula will do that and I also believe that
it will return 14th of a month in months where the 3rd Friday is on the 21st,
but I'm sure that can be adjusted.......

 

[Don Guillett]

Then, I misunderstood. So sollllly

 

[Guest]

My previous reply formula I put MONTH($C$1) instead of MONTH($A$1). I tested
even on MONTH($C$1) still work like a charm. The OP wants every 3rd of Friday
of very month. Try it yourself and find out

 

[T. Valko]

If the date in A1 >= 3rd Friday, your formula returns as the first result
the current month date when it should return the next month date. Also,
enter this test date in A1: 1/31/2007. Copy the formula down at least 9
cells. One of the results is 9/14/2007 which is the 2nd Friday for that
month.. The 3rd dow for a month will never be less than the 15th day of the
month.

Biff