Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 3rd Friday every month

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 3rd Friday every month

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


"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Zsolt Szabó" wrote in message
...
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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 3rd Friday every month

Assuming real dates, different months in C4 and etc.

Perhaps your calculation mode is set to "manual"

ToolsOptionsCalculation


Gord Dibben MS Excel MVP

On Sun, 14 Jan 2007 10:02:00 -0800, Zsolt Szabó
wrote:

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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 3rd Friday every month

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Gord Dibben" wrote:

Assuming real dates, different months in C4 and etc.

Perhaps your calculation mode is set to "manual"

ToolsOptionsCalculation


Gord Dibben MS Excel MVP

On Sun, 14 Jan 2007 10:02:00 -0800, Zsolt Szabó
wrote:

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



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 3rd Friday every month

Try this:

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

Copy from C2 all the way down as far as needed


"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 287
Default 3rd Friday every month

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



"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 3rd Friday every month

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


"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 3rd Friday every month

The winners are Teeehless (or not) mama and Daddylonglegs.
Runner-up Don Guillett. Thank you very much for the awsome fast and
brilliant answers. You made my weekend.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 3rd Friday every month

On Sun, 14 Jan 2007 08:18:00 -0800, Zsolt Szabó
wrote:

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


  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 287
Default 3rd Friday every month

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.......

"Zsolt Szabó" wrote:

The winners are Teeehless (or not) mama and Daddylonglegs.
Runner-up Don Guillett. Thank you very much for the awsome fast and
brilliant answers. You made my weekend.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 3rd Friday every month

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

"daddylonglegs" wrote:

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.......

"Zsolt Szabó" wrote:

The winners are Teeehless (or not) mama and Daddylonglegs.
Runner-up Don Guillett. Thank you very much for the awsome fast and
brilliant answers. You made my weekend.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 3rd Friday every month

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

"Teethless mama" wrote in message
...
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

"daddylonglegs" wrote:

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.......

"Zsolt Szabó" wrote:

The winners are Teeehless (or not) mama and Daddylonglegs.
Runner-up Don Guillett. Thank you very much for the awsome fast and
brilliant answers. You made my weekend.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Zsolt Szabó" wrote:

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.
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get the date for the 2nd friday of each month? dustin Excel Worksheet Functions 8 November 8th 06 11:46 PM
date of last friday of previous month tkaplan Excel Discussion (Misc queries) 7 November 14th 05 07:05 PM
Find out first Friday every month Ragdyer Excel Discussion (Misc queries) 7 September 2nd 05 12:59 AM
Find out first Friday every month noiseash Excel Worksheet Functions 3 September 1st 05 09:24 AM
Date Calculations Bruce Excel Worksheet Functions 11 May 19th 05 01:09 AM


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"