Third Friday of the even months

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
Hi. The third Friday of the month has to be between the 14th and the 22nd so how can I use these EVEN(MONTH())) WEEKDAY()=6 DAY()>14 DAY()<22) and 20/12/2013
to come up with 21/02/2014 at a cell?

Thanks for any help!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
One way:

Row\Col
A​
B​
C​
1​
Month
3rd Fri
2​
Mon 25 May 2015​
Fri 15 May 2015​
B2: =LOOKUP(2, 1 / (WEEKDAY(A2 - DAY(A2) + {15,16,17,18,19,20,21}) = 6), A2 - DAY(A2) + {15,16,17,18,19,20,21})
3​
Thu 04 Jun 2015​
Fri 19 Jun 2015​
4​
Fri 10 Jul 2015​
Fri 17 Jul 2015​
5​
Fri 28 Aug 2015​
Fri 21 Aug 2015​
6​
Tue 29 Sep 2015​
Fri 18 Sep 2015​
7​
Wed 14 Oct 2015​
Fri 16 Oct 2015​
8​
Mon 30 Nov 2015​
Fri 20 Nov 2015​
9​
Wed 30 Dec 2015​
Fri 18 Dec 2015​
10​
Sat 02 Jan 2016​
Fri 15 Jan 2016​
11​
Sat 06 Feb 2016​
Fri 19 Feb 2016​
 
Upvote 0

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
Thank you for answer. What I'm trying to do is have a table with the dates that were on third Friday of the month and the month was even.
eg.
21/02/2014
18/04/2014
20/06/2014
15/08/2014
17/10/2014
19/12/2014
20/02/2015
17/04/2015
19/06/2015
21/08/2015

Is there anyway I can do that?
 
Upvote 0

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
Row\Col
A​
B​
1​
2​
Fri 21 Feb 2014​
A2: =DATE(2014, 2*ROWS(A$2:A2), 1 + 7*3) - WEEKDAY(DATE(2014, 2*ROWS(A$2:A2), 1 - 6))
3​
Fri 18 Apr 2014​
4​
Fri 20 Jun 2014​
5​
Fri 15 Aug 2014​
6​
Fri 17 Oct 2014​
7​
Fri 19 Dec 2014​
8​
Fri 20 Feb 2015​
9​
Fri 17 Apr 2015​
10​
Fri 19 Jun 2015​
11​
Fri 21 Aug 2015​

... where 7 is the number of days in a week, 3 refers to the 3rd occurrence of a weekday in a month, and 6 is the weekday of interest (Friday).

I'm sure that formula originated with barry houdini.
 
Upvote 0

Forum statistics

Threads
1,196,027
Messages
6,012,947
Members
441,740
Latest member
abaz21

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