# Calculating every third Friday?

#### vanclute

##### Board Regular
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

L

#### Legacy 11273

##### Guest
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

##### Well-known Member
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

##### Board Regular
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

##### New Member
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

##### New Member
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

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

Replies
3
Views
315
Replies
4
Views
165
Replies
0
Views
122
Replies
0
Views
198
Replies
8
Views
277

1,181,065
Messages
5,927,916
Members
436,576
Latest member
rovman1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back