Reply
 
LinkBack Thread Tools Display Modes
  #1   Report Post  
Old January 14th 07, 04:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
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  
Old January 14th 07, 06:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
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  
Old January 14th 07, 06:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
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  
Old January 14th 07, 06:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
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  
Old January 14th 07, 07:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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  
Old January 14th 07, 07:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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



Reply
Thread Tools
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 10:46 PM
date of last friday of previous month tkaplan Excel Discussion (Misc queries) 7 November 14th 05 06: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 12:02 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017