Finding the third Thursday date of every month for the year 2017

[Kier Calimoso]

Hi,

So I am new here, and also interested in learning how to code formulas in excel. I am using excel 2016.

Would you be so kind to share a formula for "Finding the third Thursday date of every month for the year 2017"

Example:
A1: Formula
A2: input Year
A3: 1/19/2017
A4: 2/16/2017
...
Until 12/21/2017

Thanks!

 

[BrianJN1]

Forgive me, I haven't thought this through fully, just a few initial thoughts;

[TABLE="width: 221"]
<tbody>[TR]
[TD]WEEKDAY [/TD]
[TD]DAY[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]22[/TD]
[TD]22/06/2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]29[/TD]
[TD]29/06/2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]6/07/2017[/TD]
[/TR]
</tbody>[/TABLE]

In this Table the date 22/6/2017 (conveniently is today and a Thursday - actually recorded with =NOW() ).
The ensuing dates copy the cell above +7, eg, = C3+7. DAY is given using the function = DAY(C3) and WEEKDAY by = WEEKDAY(C3).
Those are dragged down.

That is going to give you every Thurs.
Right at the moment I have an engagement so cannot add conditions so as to generate what you require. In the meantime, if no-one has taken this up, I'll relook in about 16 hours.

NB: My computer is set to DD/MM/YYYY format.

 

[BrianJN1]

Ok. Yesterday I gave a means to identify every Thursday in 2017.
Now to find the third one of every month one might be inclined to merely add 28 to the last date.
That would work fine except months where there are 5 Thursdays (and that does happen in June 2017).

http://members.iinet.net.au/~brianjnow/DATE.jpg

The image contains all of the formula which I have used except for cell C1: =(C2-C3)*365.23 (and the 1899 value in C3).
Type the formulae and values given into the cells mentioned and then drag the formula in A4 down. Changing the value in G1 from 1 to 7 will obvious change your focus day.

Once everything is in place typing any year from 1900 into C2 should, I trust, yield what you require.

 

[FlameRetired]

Withdrawn by FR. Mistake in formula.

 

[FlameRetired]

Had to make small adjustment in when the week starts.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Excel 2012
	A	B	C	D
1	1/1/2017	12/31/2017	1/19/2017	Thursday
2			2/16/2017	Thursday
3			3/16/2017	Thursday
4			4/20/2017	Thursday
5			5/18/2017	Thursday
6			6/15/2017	Thursday
7			7/20/2017	Thursday
8			8/17/2017	Thursday
9			9/21/2017	Thursday
10			10/19/2017	Thursday
11			11/16/2017	Thursday
12			12/21/2017	Thursday
13
14
Sheet1

Cell Formulas
Range	Formula
B1	=EDATE(A1,12)-1
D1	=TEXT(C1,"dddd")
C1	{=IFERROR(SMALL(IF(MONTH(ROW(INDEX(A:A,$A$1):INDEX(A:A,$B$1)))*(WEEKDAY(ROW(INDEX(A:A,$A$1):INDEX(A:A,$B$1)),1)=5)*((WEEKNUM(ROW(INDEX(A:A,$A$1):INDEX(A:A,$B$1)),15)-WEEKNUM(EOMONTH(ROW(INDEX(A:A,$A$1):INDEX(A:A,$B$1)),-1)+1,15)+1)=3),ROW(INDEX(A:A,$A$1):INDEX(A:A,$B$1))),ROWS($1:1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BrianJN1]

That is the second time in nearly as many days that I've come across CSE!

Back to the task. My solution was to allow the development from the start of any year that was input since the proposer cited in his example:
A2: input year.
I know that an terminal date was mentioned but I wondered if there was some ambiguity as to one specific year, or maybe others. Thus my more general approach.

How easily might it be to rest a different year, just change the start date, ie "1/1/xxxx" in cell A1?
If the table is already built then an alteration there would automatically be recalculated?

 

[FlameRetired]

Yes. My apologies for my previous effort. I over complicated this.

This is a non array formula. With input year in A2 this in B2 and filled down. It will continue to output 3rd Thursdays into the following years as well.

Edited Corrected mistake in formula.

Excel 2012
	A	B
1
2	2017	1/19/2017
3		2/16/2017
4		3/16/2017
5		4/20/2017
6		5/18/2017
7		6/15/2017
8		7/20/2017
9		8/17/2017
10		9/21/2017
11		10/19/2017
12		11/16/2017
13		12/21/2017
14		1/18/2018
15		2/15/2018
16		3/15/2018
17		4/19/2018
18		5/17/2018
19		6/21/2018
20		7/19/2018
21		8/16/2018
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX(DATE($A$2,ROWS($2:2),{15,16,17,18,19,20,21}),MATCH(5,WEEKDAY(DATE($A$2,ROWS($2:2),{15,16,17,18,19,20,21}),1),0))

 

[Tetra201]

Hi,

So I am new here, and also interested in learning how to code formulas in excel. I am using excel 2016.

Would you be so kind to share a formula for "Finding the third Thursday date of every month for the year 2017"

Example:
A1: Formula
A2: input Year
A3: 1/19/2017
A4: 2/16/2017
...
Until 12/21/2017

Thanks!

Try placing the following formula in cell A3 and drag-copying it down:

=(ROWS($1:1)&"-"&$A$2)+21-WEEKDAY(ROWS($1:1)&"-"&$A$2,15)

 

[FlameRetired]

@ Tetra201

Very clever! Took me a while to understand how it works.

 

[Rick Rothstein]

Here is another formula you can use... assuming cell A2 has the year in it, put this formula in cell A3 and copy it down to cell A13...

=WORKDAY.INTL(DATE(A$2,ROWS($1:1),1)-1,3,"1110111")