|
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 |
3rd Friday every month
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
3rd Friday every month
Put the formula in c2 and in c3 =c2+7 in c4 =c3+7,etc
-- Don Guillett SalesAid Software "Zsolt Szabó" wrote in message ... 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 |
3rd Friday every month
But he doesn't want the next week in the next row, he wants the 3rd Friday
of the next month. -- David Biddulph "Don Guillett" wrote in message ... Put the formula in c2 and in c3 =c2+7 in c4 =c3+7,etc "Zsolt Szabó" wrote in message ... Please dont give up on me! "Don Guillett" wrote: You could use in c3 and in the others c3+7 and copy down "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 |
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 |
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 |
3rd Friday every month
Then, I misunderstood. So sollllly
-- Don Guillett SalesAid Software "David Biddulph" wrote in message ... But he doesn't want the next week in the next row, he wants the 3rd Friday of the next month. -- David Biddulph "Don Guillett" wrote in message ... Put the formula in c2 and in c3 =c2+7 in c4 =c3+7,etc "Zsolt Szabó" wrote in message ... Please dont give up on me! "Don Guillett" wrote: You could use in c3 and in the others c3+7 and copy down "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 |
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 |
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 |
| All times are GMT +1. The time now is 03:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com