Calculating every third Friday?

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
Joined
Feb 16, 2002
Messages
1,592
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
Joined
Oct 23, 2002
Messages
218

ADVERTISEMENT

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
Joined
Jul 30, 2010
Messages
21
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
Joined
Jul 30, 2010
Messages
21
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
Joined
Apr 12, 2016
Messages
128
Office Version
  1. 365
Platform
  1. Windows
Thanks for bringing up this topic, this will help me as well
 

Forum statistics

Threads
1,144,293
Messages
5,723,554
Members
422,503
Latest member
aarifmahmood

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top