A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

3rd Friday every month



 
 
Thread Tools Display Modes
  #1  
Old January 14th 07, 04:18 PM posted to microsoft.public.excel.misc
Zsolt Szabó
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
Ads
  #3  
Old January 14th 07, 06:02 PM posted to microsoft.public.excel.misc
Zsolt Szabó
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  
Old January 14th 07, 06:10 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default 3rd Friday every month

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

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  
Old January 14th 07, 06:40 PM posted to microsoft.public.excel.misc
Zsolt Szabó
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"
>
> Tools>Options>Calculation
>
>
> 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
> >

>
>

  #7  
Old January 14th 07, 06:57 PM posted to microsoft.public.excel.misc
Zsolt Szabó
external usenet poster
 
Posts: 29
Default 3rd Friday every month

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

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


"Don Guillett" wrote:

>
> You could use in c3 and in the others c3+7 and copy down
> --
> Don Guillett
> SalesAid Software
>
> "Zsolt Szabó" > wrote in message
> ...
> > 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
> >>
> >>
> >>

>
>
>

  #8  
Old January 14th 07, 06:59 PM posted to microsoft.public.excel.misc
Zsolt Szabó
external usenet poster
 
Posts: 29
Default 3rd Friday every month

Please dont give up on me!
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt


"Don Guillett" wrote:

>
> You could use in c3 and in the others c3+7 and copy down
> --
> Don Guillett
> SalesAid Software
>
> "Zsolt Szabó" > wrote in message
> ...
> > 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
> >>
> >>
> >>

>
>
>

  #9  
Old January 14th 07, 07:01 PM posted to microsoft.public.excel.misc
Teethless mama
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  
Old January 14th 07, 07:03 PM posted to microsoft.public.excel.misc
daddylonglegs
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04:43 AM.


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