Calculating every third Friday?

[vanclute]

I'm trying to create a range of dates spanning the next couple of years. I want to ultimately have a pulldown list that contains one date for the third Friday of each month. I know how to make the list if I manually type in each of these dates, but I'd like to have Excel create them for me. Seems simple enough but I can't get Excel to work with me on non-constant shifts of time like every 3rd Friday, since each month is a different number of days.

What's the trick all you Excel jockies out there?

Jonathan

 

[PaddyD]

Hi!

see the

First, Last, Nearest, Closest, nth weekday (4th Monday) in Month, Date calculations

section on Dave McRitchie's sight for wome methods:

http://www.mvps.org/dmcritchie/excel/datetime.htm

post back if you need more.

paddy

 

[Legacy 11273]

Put the date 1 Jan 2002 in A1 (or some other start month). Make sure the day is the first of the month.

Fill down as far as required (by one month increments).

Put the following in B1 and fill down :-

=A1+MOD(6-WEEKDAY(A1),7)+14
This message was edited by Bali on 2002-10-24 22:51

 

[Derek]

Hi Jonathan

Perhaps you would like to try this formula:

In B1:G1 type the year (eg 2002 / 2003 /2004 etc)
In A2:A13 type the month number ( eg 1 / 2 / 3 etc)
Now paste this formula into B2 and scroll it across and down:

=(1&"/"&$A3&"/"&B$1)+(20-WEEKDAY((1&"/"&$A3&"/"&B$1),1))+IF(WEEKDAY((1&"/"&$A3&"/"&B$1),1)=7,7,0)

Format the cells for date. This will give you the date of the 3rd Friday in any month for any year in the format day, month, year (I'm an Aussie!)

Hope this helps

regards
Derek

 

[vanclute]

Thanks everyone for the solutions! The simplest (and first I tried) seemed to be the second suggestion, which by having the initial cel A1 contain =TODAY() and filling down from there, I get a list of months out to 2004 which will constantly update and always give me that many relative years from today. Then the formula as suggested gives me every 3rd Friday for each of those years. So I should never have to touch it again, I will always have a dynamically updating list of every 3rd Friday for the next 2 years.

Thanks again! Funny how the documentation just really doesn't give as clear an explanation of this stuff...

Jonathan

 

[richardlh]

Thanks everyone for the solutions! The simplest (and first I tried) seemed to be the second suggestion, which by having the initial cel A1 contain =TODAY() and filling down from there, I get a list of months out to 2004 which will constantly update and always give me that many relative years from today. Then the formula as suggested gives me every 3rd Friday for each of those years. So I should never have to touch it again, I will always have a dynamically updating list of every 3rd Friday for the next 2 years.

Thanks again! Funny how the documentation just really doesn't give as clear an explanation of this stuff...

Jonathan

Try this formula in A1 to get the current month and year: =MONTH(TODAY())&"/"&YEAR(TODAY()). Then include the formula =A1+MOD(6-WEEKDAY(A1),7)+14 stated previously in B1. The formula in A1 will update automatically without the day, just the month and year to keep the formula in B1 from giving erroneous results.

Regards, Richard

 

[richardlh]

Take this one step further:

Start a new worksheet and make sure you have the EOMONTH function available. Then put the first two formulas in A25 and B25

A25: =MONTH(TODAY())&"/"&YEAR(TODAY()) B25: =A25+MOD(6-WEEKDAY(A25),7)+14

Then in the next row put the following formulas:

A26: =EOMONTH(A25,0)+1 B26: =A26+MOD(6-WEEKDAY(A26),7)+14

Copy these formulas from A26:B26 to A49:B49. This will give you the third Friday of the month for the next 24 months.

Then in the row above A25 put the following formulas:

A24: =EOMONTH(A25,-2)+1 B24: =A24+MOD(6-WEEKDAY(A24),7)+14

Copy these formulas from A24:B24 to A1:B1. This will give you the third Friday of the month for the previous 24 months in case you want to do some back testing.

Thanks for Bali for previous formula, Enjoy!

 

[AbrahamGluck]

Thanks for bringing up this topic, this will help me as well